What will you do when you get two customers of the same name? What will you do when a customer changes their name? Relating tables by name has potential issues that you can avoid if you use an auto-entered serial number instead of their name in order to define a relationship between the two tables.
A typical relationship between two such tables would be:
CustomerInofrmation::__pk_CustomerID = Data::_fk_CustomerID
And there would be no customerName field in Data. Instead, you'd add the existing CustomerName field from the CustomerInformation table where needed to display the customer name.
I'm curious as to why you want the approach you describe in your post.
Why do you need such a specified order?
How will you update the value in order when a customer changes from active to inactive status?
Will you renumber when this happens or leave gaps in the series?
How will this affect your data entry process?
I'm asking these questions as there may be simpler ways to set up your layouts for data entry.
I really don't want to divulge exactly what I am doing because it may be a marketable product in the industry that I am doing it for but here is an example..
Collecting statistics for a baseball team. I want to be able to cycle through the lineup automatically. The RosterTable may include more than 9 players so I will be indicating which ones are currently playing and what their order in the lineup is.
There will never be gaps in the series and changes will be made manually on another layout.
Do you need to create the new records in Data one at a time or would it work to press a button that pops up the complete list all in one "batch" operation.
If you want the list to appear all at once, you can use this script and the relationship I already specified:
Go To layout [CustomerInformation]
enter FInd mode //clear the pause check box
Set field [CustomerInformation::Active ; 1 ]
Set Error capture [on]
Sort [restore, no dialog ] //sort them by the order field
If [Get ( FoundCount ) ]
Go to layout [Data]
Import Records //specify an import from CustomerINformation, only import the __pk_CustomerID into _fk_CustomerID
Show Custom dialog ["No active customers were found"]
To show the customer name, put the name field from CustomerInformation on your Data layout.
Let me know if you want the records to be created one at a time as that can also be done.
My idea was to create the next record after the current one is finalized, so one at a time.
This would allow for possible changes to the active list on the fly.
I'd still keep the relationship I specified for linking data recors to customer records as it's safer, but this relationship can be added for enabling your "next customer" capability:
CustomerInformationByOrder::Order = Data::gNextCustomer
In Manage | Database | relationships, make a new table occurrence of CustomerInformation by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be: CustomerInformationByOrder.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Define gNextCustomer to be a global number field.
Put a "next Customer" button on your Data layout that performs this script:
Set Field [Data::gNextCustomer ; Data::gNextCustomer + 1]
Set field [ Data::_fk_CustomerID ; CustomerInformationByOrder::__pk_CustomerID]
At the start of each session of entering data, clear the gnextCustomer field or set it to zero. You can do that manually or with a button.