Thank you for your post.
When you enter a purchase number in the Customer table and skip to the next field, FileMaker should automatically display all records from the purchase table that matches that purchase number.
Since your key field is a calculation, what is the formula of your calculation?
Here are the steps I took:
1. I created a table "Primary" with three fields:
First Half (Text)
Second Half (Text)
CalcKey (Calculation) = If (IsEmpty (First Half) or IsEmpty (Second Half), "", First Half & Second Half)
2. I created a second table "Secondary" with three fields:
3. I entered the following four records into "Secondary"
(Key - Field1 - Field2)
A1 - First A - More First A
A2 - Second A - More Second A
B1 - First B - More First B
B2 - Second B - More Second B
4. I created a relationship between "Primary" and "Secondary" table using "CalcKey" = "Key"
5. I switched back to "Primary" and created a portal into "Secondary" and showing all fields.
6. In Browse, I create a new record and enter information into First Half and Second Half. For each value, the information appears in "Secondary"
If I enter a new values:
First Half = "C"
Second Half = "1"
... CalcKey = "C1", and no record appears in "Secondary". If I enter information into the portal, the value "C1" will then be added to the "Key" field in "Secondary".
Let me know if I missed something, or let me have your example, and I will try to duplicate it here.
"Instead of entering this purchase number for every purchase, I specified a validation function that simply says, add the text to the customer number and store it in the purchase number field. It works great...under some conditions."
An auto-enter calculation should be used for this, not a validation calculation. Validation is used to check whether the field entry is *correct* or within a range. Auto-enters are used to automagically type things into fields.
The auto-enter calculation could be:
PurchaseNumber & "INT"
... if the text never changes, or to make the text the first 3 characters of the person's last name use:
PurchaseNumber & left( LastName ; 3 )
These calculations assume there are fields named "PurchaseNumber" and "LastName".
When setting up the auto-enter calculation, it can be made to calculate only if the field is empty, or every time the fields in the calculation change. You need to decide whether the purchase number should change once it's been entered or stay the same, even if the person's name is changed.
I'd not recommend using this field as the primary key for a relationship. Instead use an auto-entered serial number.
Thank you TSGal. It's going to take me some time to work through what you have suggested here (because I'm a newbie). However, I did want to thank you for giving this suggestion.
Thank you...stay tuned.
Because I'm new to FM, I'm afraid that I used a term incorrectly and your main suggestion is based on my misleading info -- sorry about that.
More specifically, I AM using the auto-enter calculation and NOT the validation, as I initially indicated.
My problem however is that if I -- for example -- edit an existing record, and while doing so, enter data into the CustomerNumber field, the auto-enter function does not work in my portal, unless I "force a save" after I enter the CustomerNumber. When I say, "force a save," it simply means that I go-forward in the database, and then back to the record.
I tried this, but it did not work. I wrote a script called dhFlushCache and it simply had a "flush cache to disk." I would then edit a record by entering the CustomerNumber, and then run the script (which I hung off Cmd-0). As I said, I THOUGHT this might work, but it did not.
Thank you for the clarification.
Yes, if you enter information into the portal, the data isn't committed until you exit the record. However, if you have the option to "Allow creation of records in this table via the relationship" turned on, then you should be able to tab to the next record in the portal, and the data should be updated. This option is under File -> Manage -> Database. Click on the Relationship tab, and then click on the icon that connects the two tables together. You'll see the option on both sides for each table.
Let me know if this helps.
Thank you for this help. I'm starting a 3-day FM course on Monday and really looking forward to it.