Link to home
Start Free TrialLog in
Avatar of c0link
c0link

asked on

Equijoin using global field

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?
Avatar of Member_2_908359
Member_2_908359
Flag of France image

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
Avatar of c0link
c0link

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Member_2_908359
Member_2_908359
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of c0link

ASKER

No problem but how do I do that? You mean send it to you?
yes, or post it on a web/ftp site where I can retrieve it.
Avatar of c0link

ASKER

So where do I send it?
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).
Avatar of c0link

ASKER

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.
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...
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)
Avatar of c0link

ASKER

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
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
Avatar of c0link

ASKER

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.
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.