You'll need to set up a relationship between the social_security_number field in the NAMES table and the equivalent field in the ACCOUNT_INFO table. This is done on the Relationships tab in the Manage Database dialog (File > Manage > Database). Just drag and drop the field from one table occurrence to the other.
Once you've done that, you can define the fields you want as lookups under Options (lower right-hand corner of the Manage Database dialog).
As an aside, it's usually not necessary to perform lookups of information from one table to another. Except in unusual circumstances, you can use the related fields from the parent table directly on your layouts and in portals without having to copy it over. It's poor database practice to keep data in multiple places, so avoid it if you can.
Thanks you so much for your help. Do you know if it matters in which table I do the join? Also, can the Social Security field be made unique so that I do not end up with duplicates?
You don't do the join in a table. You do it on the Relationships Graph, like this:
When you set up your data entry, you can do it in a number of different ways, but the simplest would be a pull-down list that shows the Social and the person's name. This allows the user to select the appropriate person and then everything lines up appropriately. I've attached a sample.
(Yes, you can validate any field as unique through the Options tab. See the sample.)
P. S. I apologize for using the .fp7 format for the sample. My computer is chewing on a data update in 12 at the moment.
SampleForBob.fp7.zip 5.6 K
1 of 1 people found this helpful
Thank you so much for the sample, and the advice. This has given me some great insight into where I need to go with this If I may, ask another question?? I would like a portal to list all names and socials (I assume that it should) that belong to the account number(s) listed in the table. I cannot seem to get the portal to list all associated people. Examaple:
I have 5 people's names, 3 accounts numbers (that's IF there are threre account numbers for this transaction) that they belong to.
Account 1 Account 2 Account 3 Cash in Cash Out
Social Security Names
123-56-6789 Joh Doe
987-65-4321 Jane Doe
654-321-9888 Sandy Beach
000-222-5444 Jim Shoe
999-88-7777 Santa Claus
So basically pulling names and socials from the "names_social" table into the "accounts_transactions" table when a user types in the portal (if there is not an existing customer, let the name and social get added through the portal). I hope this make sense.
Thank you so much