Solved

Equijoin using global field

Posted on 2006-11-12
14
242 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
  • 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now