So, if a name is changed in Customers, the samples and/or sales information won't show in the portal, because it the names have to be identical for the data to show up. I have considered just locking the Company Name field in browse mode, to avoid it ever being changed, but that is impractical because company name changes occur, and when entering a new contact, you'd have to unlock and relock constantly, which isn't ideal for the user
One should not link records by a name--either of a person or of an organization. You have posted one reason. Here is another: These names are not unique both people and companies do not have unique names.
You need to define a field in either customers or in a new, fourth table called companies (if you might have more than one customer from the same company) defined as a number field with an auto-entered serial number. For existing records in your table, you will want to assign a serial number value to this field using replace field contents with the serial number option and be sure to select the option that updates your serial number settings when you do so.
Then your three tables should be linked by this serial number instead of the company (or customer) name. This way, if a customer or company name needs to be changed, you change this value in a single record in one table and none of your links to other records via relationships are affected. You can include the name field on layouts based on your related table by just adding that field from customers (or a new company table) to the layout. The relationship will link to the correct record and show the name as though it were part of your layout's table.
When needed, you can set up a "use values from field" value list where the first field is this serial number and the second field is the name. This allows you to select a company (or customer) by name, but the value list enters the ID number.
This value list, is the first step "beginner level" method for linking records by ID number. In place of a serial number, it's also possible to set up a text field that auto-enters this calculation: Get ( UUID ). This produces a very long, but unique string of characters that also uniquely identifies each record. It's a bit more cumbersome to work with, but has some advantages in use as well as disadvantages.
For a look at a more sophisticated way to link records by ID that makes for a more user friendly value list than the above "ID field/Name Field" value list (some scripting required), see:
One of the examples is a text field set up with an auto-complete enabled value list of names, but with a script that handles names not found in your table as well as duplicate names to make for a better way to manage this process when the number of names in your value list become too long to conveniently scroll through.
Better read my last post on serial numbers again.
Auto-entered serial numbers will get assigned to each NEW record that you create. To assign serial number values to your records that already exist in the table, you will need to use Replace Field Contents with the serial number option. Be sure to select the setting that updates your auto-enter options so that the "next serial value" setting on the field also updates to avoid assigning duplicate values to your next new record.
I have successfully added serial numbers to my customers, 385 in total. What I need to do next, the step I'm having issues with is connecting these serial numbers with my Samples, and Sales tables. I'd like the serial numbers 1-385, from my customers to be assigned correspondingly to the records I have in my Sales and Samples, so that they show up in my portals I have displayed in tabs on the Customers table
Have you updated the relationships to match by serial numbers yet? If you haven't, don't as there's a simple update process you can use before changing over the relationships to match by serial numbers. If you have, temporarily change them back to what you had previously.
If you haven't defined match fields for the serial numbers, define a new number field to use as this match field. Keep the original company fields in place for now and delete them once you've made the change over to serial numbers.
Now go to a layout based on samples. Put the new serial number field on your layout. Return to Browse mode and show all records. Click into the serial number field and select Replace Field Contents. Select the calculation option and enter this expression, but use the name of your customers table::field if it doesn't match my example:
This will use your current company name based relationship to copy over the new serial number from the matching customer records.
Repeat these steps on a layout based on Sales to copy over serial numbers into your sales records.
Now you can go into Manage | Database and update your relationships to match by serial numbers. Check to make sure data now appears as it should in your portals before removing the fields for company name in your samples and sales tables. If you need to show a company name on a samples or sales layout, just add the company name field from the customers table.
Thank you! I have successfully linked the tables via serial number, and all my records are showing up as expected. BUT I my original problem hasn't been solved- when I change the name of a Company in my "Customers" Table, I want it to automatically make that change in both "Sale" and "Samples" tables.
Yes it has. Recall that I told you to remove the company name field from your sales and samples tables as this field is redundant and no longer necessary. On any layout based on the sales or samples table, add the company name field from your customers table. When you modify a company name, all related samples and sales records will also show the change as they are referring to the same exact field from the same customer record.
I must have done something incorrect along the way because now I am having deletion issues; changing a name in the main customer table for example results in the deletion of the corresponding sample record, because the name isn't verbatim.
That may be what it looks like, but it's not what is happening.
It sounds like you didn't get your relationships updated to match by the new ID number fields. Thus, changing a name in the main table breaks the link to the related record and it drops out of the portal. The record was not deleted, it just won't show in the portal as it is no longer a related record.
Fix your relationships to match by ID's and this should no longer be the case.
Ok, I have attached screen shots of what I have set for relationships now. With them set this way, no records show up in any portals, and remains in Samples and Sales, but the "Company" field appears with no data in it.
When I change the relationship to "Company = Company" all the portals are full and the records appear in all tables as it should, but if i change any company name it creates new records, or records stop showing up.
With them set this way, no records show up in any portals, and remains in Samples and Sales,
Sorry to say this, but then clearly, you do not have matching values in the ID fields of the samples and sales tables. I've provided you with a detailed description of how to get those values into place, but something didn't go right for you there or you would not see the results that you are describing now.
And you have very dangerous Delete options specified in your relationship. With the relationships specified, deleting a single samples record will also delete the linked customer record and all other samples records linked to that customer record. This is generally not a desirable result as you often need to delete a child record (samples) without deleting the parent(customer).
The fields in those tables, BTW, should be named _fk, not __pk as those are foreign key (fk) fields that match to the primary key (pk) field in customers. That doesn't affect how they work, but it can cause confusion when you discuss your design with others. (The two underscores for a pk and one underscore for an fk are there so that an alphabetical listing of fields from your table will list the primary key field first followed by any foreign key fields.)
You have been a tremendous help, I have successfully linked my tables via serial numbers. The last (hopefully last!!) bug I have is that since I have it set up in such a way that information entered in the samples/sales portal rows creates a record in the sales/samples tables, when I enter a new sample or sale, the record is indeed created in the corresponding sample/sale table, but the company name doesn't copy over to the new record.
But you don't need it to copy over!!!
This is exactly what you do not want to do. If you store the company name in both tables, you will have problems whenever you need to change a company name.
If you set up a layout based on Samples and need to show the company name, here's how you do it:
Enter layout mode. Use the field tool to add a field to the layout and thus open Specify Fields or open the Field Picker. The the top of Specify Fields and the Field Picker, you have a drop down list of table (occurrence) names. Select your customers table so that the dialog box now lists the fields from the customers table. Select the company name field from this list in order to add it to your layout. This field, which is the field from the company table will then show the company name IF that sample field is linked by company ID to a record in the company table.