"I have created a key field of vendor number PLUS check date"
What syntax? VendorNumb + CheckDate (incorrect) or VendorNumb & CheckDate (correct)
In any case, in FMP you can now match up multiple pairs of fields instead of using a calculated key. You can set up your relationship in this form:
Table1::VendorNumber = Table2::VendorNumber AND Table1::CheckDate = Table2::CheckDate.
BTW why use a "date" instead of the check number? Surely the same vendor could make purchases on two or more invoices in the same day and this would give you two checks with the same vendor ID and check date.
I did use correct syntax: vendnum&" "&year(ckdate)&right("0"&month(ckdate),2)&right("0"&day(ckdate),2)
At the time the PreCheck report runs check numbers have not been assigned yet. I am trying to use existing spooled reports to populate my tables. Even in the scenario when there is one invoice on the one check it is not working. It is not material how many invoices are generated on a given day (that would be invoice_date).
The calculation above should generate as unique a number as the check number in order to complete the relationship. Just nothing.
I assume the "check date" is the date the check is written? If so, then a customer that makes a purchase on two or more invoices on the same day will write their check on the same day right? That's the way I'm reading your description of your process anyway and that will defeat your assumed uniqueness here.
In any case, given the complexity of your calculation field, I'd pull up the fields in question side by side an make a very meticulous examination of each. If even one character is different from what's needed for the fields to match values, you'll get zero matching records. Something as simple and subtle as comparing a zero with the letter 'O' or an extra space is all that it will take.
These are vendor check checks. Check date is indeed the date it is written. Whatever invoices we have in payables the meet the criteria of due dates will be paid. Uniqueness on the side of the invoices is not relevant.
Each check can have multiple invoices. The Check Table can only have one check number for each vendor on that date. It is a one-to-many, one check to many invoices.
I have actually copied the key field from the Check Table pasting it into the key field of the child-table (invoices), and it has successfully pulled up the invoices. I create the fields names with the exact same spelling. CkDate is always the check date in any table. Vendor_Number is always the vendors' number in all tables. I can cut/paste the formula from one spot to another to ensure it is correct. That is why I am so baffled.
Is this a stored, indexed calculation? If you look at the field in the field definition list and see "unstored" then that would explain the lack of matching records. While you can get away with using a global field or unstored calculation for one side of a relationship (The table you base a layout on), the other half (the table you base the portal on) must be indexed.
Are the calculations' return types text?
In the Check table VEND_CkDate is defined as Text with a calculated value. Calculation is made on import. On the Invoice table it is a calculation field. Everything is set to store.
"On the Invoice table it is a calculation field."
Is this calculation defined to return number (the default setting) or text?
It is set for Text.
I'm running out of suggestions frankly.
How exactly have you set up your portal? Any chance it doesn't refer to the correct table occurrence?
I double checked that ... several times. I am connecting the final check table (there is a scratch table) and the final remit table (again scratch table). I use scratch tables to clean the data before it is final. Once it is in final tables, it cannot be deleted.
I really do appreciate the time you have given me on this. This is not the first time I have done relationships or created portals. I have been working with Filemaker since version 3. I did not anticipate problems on this piece of my puzzle. I thought it would be smooth sailing after I licked the import and parsing problems.
It's probably some picky little detail you're not seeing and I haven't asked the right question to identify. You may need to get someone to physically examine your design and data or you may need to take a break and come back and look at with a fresh perspective.