Solved

Equijoin using global field

Posted on 2006-11-12
14
246 Views
Last Modified: 2010-04-27
I have set up a commission table which has a fields Year_Written and Year_Paid. I have set up a global field in the same table called Assessment_Year. I have tried to set up an occurence based on this table, with equijoin on Commission_ID and Year_Written to global field Assessment_Year, to limit records to those where the Year_Written match the Assessment_Year. However,  the dataset comes up blank.

If I experiment by matching the Year_Written with Year_Paid I get the record set I would expect.

Is it not possible to make an equijoin with a global field?
0
Comment
Question by:c0link
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 28

Expert Comment

by:lesouef
ID: 17931613
it is, but your layout should be linked to the 2nd occurence of the table, or you should show yr given year data through a portal showing 2nd occurence records in the 1st table layout. not sure this is clear... tell me is that makes sense, but don't worry, you can display the given records with a global field to filter the visible records
0
 

Author Comment

by:c0link
ID: 17935969
I have checked that my layout is linked to the second occurrence table Commission_2, which is joined on Commission-ID for both tables. The second criteria join is between Year_Written from Commission_2 table and Assessment_Year (global field) from the table Commission. Whatever the year, the records are still not limited to those records in the occurrence table where the Year_Written value is equal to the Assessment_Year value in the original Commission table. (I have checked field data types are the same).

However, and oddly, if I change the layout table to Commission (original table) but still have the Commission_2 occurrence linked, the records sort, albeit that they retain blank records for all the remaining non matched fields other than the global field which still displays. A subsequent sort on this record set does not further remove the records with blank fields.

I don't understand why linking the original Commission table to the layout comes out with the filtering that I would expect in the occurrence table.

I do not want to use a portal as I want this record set to be the underlying recordset for a report layout.
0
 
LVL 28

Accepted Solution

by:
lesouef earned 200 total points
ID: 17939575
Can I see the file?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:c0link
ID: 17943771
No problem but how do I do that? You mean send it to you?
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17945241
yes, or post it on a web/ftp site where I can retrieve it.
0
 

Author Comment

by:c0link
ID: 17945582
So where do I send it?
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17945780
If you've got no site, you can use this as a temp solution:
http://www.milleniumhandandshrimp.com/upload.html
and let me know what will be the name of the file to retrieve (avoid fancy characters and spaces!) (this is shown at the end of upload).
0
 

Author Comment

by:c0link
ID: 17945854
BusinessTracking.fp7

Commission by Year is occurrence of Commission that I am trying to limit records to. The layout I am using to view the recordset is Test. The field names are slightly different to what I gave you before (tried to make my question clearer) but I am sure you will see the links clearly enough.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17948012
ok, it's clear now. you can't do what you need without making a scripted find or using a portal. by modifying your layout I can get only say 2004 records visible, but the amount of found records stay the same, so you see all 2004 records and interleaved blank lines in between, not smart, especially when the top lines are all blank. what yr point against portals? they can be full screen, so you can the same almost...
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17948260
and also why do you use the kp_commissionID in the commission_by_year link? that will reduce the records set to records from the same year and same kp_commissionID. Is that wanted? if yes, then the commsion ID should be mentionned...
And if you intend to show only records for the current ID for a given year, I think a records filter using an event plug-in would be far easier to setup. It allows to trigger a script (which can make a find) upon a field data change (in yr case the current year); every time you would alter the current year, it automatically executes a script. Probably better in you case, but think of the amount of clients where you need to install the plug-in (free by the way)
0
 

Author Comment

by:c0link
ID: 17952556
I suppose I was thinking that I need the Commission-ID as the initial link between the occurrences but in actually fact this is integral and I need only link between the two fields that I want to match and limit the records by. I want to show all records for a given year.

The portals thing is just my limited new understanding of how they work. I don't actually want to use the layout that I have developed, it was simply a means of testing whether my sorting and linking was working properly or not. I do however want to be able to set the same sort parameters for a report layout.

Although I don't know how to do it yet, the next step would have been to sort by year using drop down box and trigger an event script to resort the data. If this is best done through an event plug-in that is available, let me have it. Where do I get this?

I think I am there with what you have described. In a nutshell, the sorting issue (allowing for my joins) is one that is only really resolved for what I want to achieve by scripting. That's no problem, I just need to be pointed in the right direction. Thanks
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17954192
the plug-in is on http://www.softs4humans.com/FMPro_Plugins.html and is called eventscript.
as far as reporting is concerned, a simple layout with a sub-summary part based on the year field is enough to summarize things on paper. Use the preview mode to see the effect once records are sorted by year, sub-summaries don't show in browse mode, only in preview mode and print mode
0
 

Author Comment

by:c0link
ID: 17954283
Thanks for that. Can't believe that this feature is not already part of FMP and I am getting to quite like FMP over MS Access.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17954326
true, data entry control is still rather poor in fm8 compared to many other products, but fm is still the best to build something quickly.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
audit trail 6 208
FM - How to Tell if a Container Field is Empty 4 175
Help with getting the most recent date in FileMaker Pro 9 10 76
Add 2 fields and get a totla 3 16
Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question