You shouldn't need an additional table.
You can set up a layout based on the invoices table for reporting referrals.
You can also set up a new occurrence of the invoices table and link it to contacts by this referral field if you want to put a portal listing all referrals on your contacts layout.
Thanks Phil. If I link an occurrence by the referral field, would I be safe from name changes, etc? I always try to link by serial.
The referral field should be an ID number matching to the PK ID field in Contacts.
Ok, do the following relationships make sense?
I would have Customers::_pkID-<Invoices::_fkID, as well as Contacts::_pkID-<Invoices::_fkReferralID? ( 2 _foreign key fields on the invoices table)
Yes but you need two occurrences of invoices--each with different names such as:
Ok, I have the relationships in place. I added a field on my invoices layout that allows the user to select the creferrer from my contacts table via a name search portal and Set Field function (when user clicks the name of the contact, it enters it into the invoices::referrer field).
Is this all that is needed? I think I am missing something (Is that what you meant by "another layout" in your initial post? Do I need to have a layout based on the second table occurrence for thsi to work? If so, is there any way to put the field from the second table occurrence on the invoice layout?) Thanks
I mentioned two options:
Set up a layout based on Invoices 2 that allows you to list referrals with sub summary parts to serve as sub headers. This could be used to list contacts in the sub headers with the referring invoices that listed that contact in the referrals field listed under each sub heading.
Put a portal to invoices 2 on a contacts layout. This will list all invoice records that listed that specific contact in the referral field.
Phil, I got that part. I am going to go with the portal on the contacts layout. I am having trouble undertstanding the relationship however.
When a New Invoice is created, the fk is filled in by the Customer pk via Customers::_pkID-<Invoices::_fkID.
How does the fk for the referral field get filled in by the Contacts pk (Contacts::_pkID-<Invoices ::_fkReferralID). Aside from the relationship, I think I need to add a set field for the _fkReferralID field containing the Contacts::_pkID from the name search portal. Is this correct? If so, Do I set field on Invoices or Invoices 2?
Set field is an option and since your layout would be based on invoices, you would reference the field in Invoices, not invoices 2. Keep in mind that you still have one table here, just two "boxes" in manage | Database | relationships with different names that both refer to the same data source table.
You can also set up _fkReferralID with a drop down list where the first (primary) field enters the contact ID and the secondary field displays the contact name.
and if you use the method in this demo file, you can even set up a text field with an auto-complete enabled drop down list of names yet a combination of script and data lookup enters the ID number to establish the needed ID number based link:
FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7
Thanks Phil. I am using the search portals because it recognizes all letters in a name and will help cut back on users "not being able to find the contact" and creating a duplicate. A popup or drop down would be cleaner, but I fear users making a mess of the contacts table creating duplicates. Any way to have a drop down behave similar to my search portal?
Take a look at the demo file. It uses scripts to improve on the user experience, detecting and preventing duplicate can be part of the process. In fact, with customer names, I'd expect you to get duplicates. My strategy for that is to use a scripted method where selecting a name that exists in more than one contact record pops up a list of all such matches with additional data--such as an address to enable the user to identify the correct name to select.
Preventing duplicate contact records with 100% success may be impossible to achieve no matter how carefully you design your system to prevent it. If you get two contacts with the same address and or phone number, they might be the same contact after a change in name or with a misspelled name. Or it could be two people that are located at the same address and thus have identical contact info. (And they might be named John Smith Jr. and John Smith Sr. and neglected to tell you they were jr or sr.) Identical names with different contact info could be the same contact after a change in address or phone number or it could be two different contacts with the same name.
Messy, but such is the nature of managing customer contacts.
Phil, I reveiwed theDemo file. I still prefer the name search portal. For example, in theDemo file, typing "George" yields zero results. Therefore the user has to know the correct first letter of the name. I prefer to give the user options to search first name, middle name, last name, letters of the name, etc. Can this be done with the dropdown?
Nope. That's why I also have a demo file with a filtered portal that uses pattern match--which sounds like the method you are using here.