You need to give more information about the relationship you want. I can't tell anything about your picture. One I don't use Access and 2 there is not information in the picture.
Are you trying to list the records in EE that DON'T have a matching record in SAP?
You can do that with a find in FIleMaker rather than a relationship.
On your EE layout, enter find mode and specify the * operator in the Sap::SAP field. Select the Omit option and perform your find.
Thank you for replying S. Chamblee and Phil.
I want a relationship between the two tables that shows all of the employees that are in EE but not in SAP.
I want a relationship instead of a find so my users don't have to remember to perform the find or setup a script.
I apologize if my picture was not clear,
The MSAccess 2003 join between the tables and the CRITERIA "is null"
DISPLAYS the employees in EE that do not have a matching record in the SAP table
I never suggested that your user should have to remember to perform the find. That would be scripted and performed by a script trigger to be performed automatically.
A filtered portal could also list such employees.
BTW, what you show in Access is NOT just a relationship. What you show is a query--the Access form of a find where the search criteria filters out the Employee records that do not have a matching SAP record.
As always, you've come through Phil!!!
Thank you very much with searching tip, * , omit as the operator for finding no match records
A script trigger will DEFINITELY do what I need.
Thank you :)
What you have encountered here is one of the more interesting (IMO) distinctions between the basic function of an Access and a FIleMaker database. For a form or Report in Access, it's based on the record set produced by a SQL query such as the one shown in your screen shot. This is a fairly "rigid" set up and if you want the user to be able to select different groups of records for the form, you have to design the needed system so that they can enter/select criteria and a VB script then modifies the underlying query and uses it to update the form's record set.
In FileMaker, the layout is based on a "found set" that is much more "fluid". At any time, the user can interact with this found set, omitting records, showing all records, performing a find to produce a different set of records without the need for us developers to add in any kind of system for enabling the users to do it.
That is a very good thing in many situations as it saves us the trouble of having to try and anticipate all the different ways the user might choose to query a given table. But in other cases such as this one, where we want that "rigid" record set and have to keep users from accidentally pulling up records that we don't want them to access on this particular layout, we now have to put in an extra effort to "lock down" the possible found sets that a user might produce--which is best done with a custom menu set and possibly a script trigger performed script to constrain any found set produced by the user down to just those that are "permitted" for this layout.
I DEFINITELY prefer FileMaker over Access, but sometimes my mind goes into auto-pilot trying to do similar procedures within FileMaker.
Old habits DIE-HARD!!! :( :)