This is similar to entering a customer's name and address on an invoice. You need a table with the court name and address and an serial ID for that record. You would then relate the ID field to your court record so that when you selected that drop down ID the name and address data would either appear as a related field or you could duplicate the fields in the court record and have them permanently entered via a lookup. The choice depends on if you need the name and address data to be "forever" or if you want it to change over time if the court gets a new address, etc.
If you are using FM 11 take a look at the Invoices Starter Solution.
Invoices relations seem way too much for what I need
Ok I have main Main Table and my Court Table. I created a courtID, which is set to auto-serialize on the Court Table. I made those two related. I am able to setup a portal with the fields needed from the Court Table on the Main Layout with everything from Main Table. I am able to get it to do a drop down and see the Court Name, but I cant figure out how to make the City, State, Zip and Phone appear
i have the fields there of course but it only enters the court data and non of the other, i think if i can figure this out then i should be able to figure this whole database out noce i figure out how to do these relations
You first need to decide whether you need this data to be physically copied over from the other file or whether a dynamic link to the related table will serve. The results, at first glance, seem identical but there's a key difference. If you use lookups to copy the data over, subsequent changes to information in the lookup table won't affect what you see in your schedule table where this data was looked up. If you use a dynamic link, any updates in the lookup table automatically appear in all records of the schedule table that refer to the record you've updated.
Both require that you define a relationship that links your two table by the CourtID field Bumper suggested, but the implementation details vary from that point.
Which approach do you want?
I doubt that court names and addresses will change, but I think it would probably make sense to make it dynamic anyway. You never know right. So yes I'd rather be able to change the Address for that court and have it update on all files
While the data isn't going to change very often if at all, you could find the need to correct data entry errors in this table and a dynamic link makes this a "fix it in one place only" operation should that be needed.
Please note that this is simply a step by step description of what Bumper suggested in his response to your question.
In your court info table, You need a CourtID field defined to auto-enter a serial number. You would define it as an auto-entered serial number. Define a matching number field in your Schedule table.
You need a relationship:
CourtInfo::CourtID = Schedule::CourtID (Use your table names here in place of mine)
On your Schedule layout, format Schedule::CourtID as a drop down list or pop up menu. Set up a value list for it that lists CourtInfo::CourtID for column 1 and a Name field from CourtInfo for column 2.
Now, on your schedule table, simply add the desired address and other fields from CourtInfo to your layout.
When you select a court from Schedule::CourtID, the matching fields will display the information from the fields you've selected from the matching record in the CourtInfo table.
Is all of the address information in the same table as the court name?
If yes, then when you create the field in the court record "point" it to come from the court name table. It should read something like courtname::address. etc with courtname being the name of the table that holds the address information. Keep in mind that doing it this way will give you the ability to alter the information in the courtname table and that if that information is ever changed it will be changed in all records that are linked to that one courtname record. You can prevent this by not allowing users to enter the field in Browse mode or do the whole name and address as a calculation field and point to that field instead of the individual fields.
If not in the one table you can still pull the address information through the courtname table as FM allow multiple hops through related tables, sort of any friend of a friend is a friend of mine.
Does the court ID need to be serialized? Reason I ask is I would rather choose the drop down menu as the county not the ID#
What I've described is that the SerialID field and a name field (such as the county) are both listed in the drop down. As a user, you select the name, but the drop down inserts the ID number.
While this can be made to work with the county name, this can create problems for you that using a serial number avoids. Specifically, you may have an issue if a county opens up a second court house--in which case county name is no longer unique. And if you mistype a county name enter data in your schedule table and then discover the typo, correcting the mistake in the courtinfo table will "break" the link to the schedule records for that court until you find the schedule records and correct each one of them to match the corrected name.
Given that you have a relatively small number of county names and they are very unlikely to be changed, you may choose to use county names anyway--that's your choice to make, just be warned and prepared for the possible pitfalls.
Perfect, I got it to work the way I needed. I thank you guys. I am sure you will see more posts from me soon, lol.