Check out a related record that you know has data in it (TotalHygeine::Property Address for example) and verify that your unique identifier TotalHygeine::HdrArea is populated correctly.
Your link looks good, but both related fields must be populated identically for it to work.
the linked fields must contain the same data and type in order for the relatioship to work
Side note: Is there a reason you have these separated into different tables if there will always be a 1:1 relationship? Doing so makes you come up with a way of populating a foreign key. If they were all one table, it would be a non-issue. Do you get a benefit from having the tables separated? What is that benefit?
ninja is right, i 'm mean what's the point in creating multiple tables if the linked fields are the same, couldn't u just transform'em to one table?
If it was up to me the would all be in one table. I am not the one who produced the data, to I have to take it the way it came. Combining the tables would be easy if all the tables had the same ammount of records, but they do not.
When these tables were imported to FMP, they came from a csv (may or may not be important). And it did not ask if HdrArea was unique or a serial #, I am not sure how to go back after the fact and set that up.
if u have a relationship like this:
table A::idA = table B::idB
the data will only show properly if "idA" and "idB" contains the same date (both contains "1" for example), so u need to make sure that ur data is compatible.
The ID's in AAAA would be as follows: 1,2,3,4,5,6,7,8,9. But in the remainder of the files, it might be: 1,3,5,9 or 2,3,5,6 or 1,2,3,4,5,6,9. Yes this would be much easier if it was in 1 table. That is in fact the only reason I am attempting to make this relational because I dont know how to get these records in 1 table.
like i said the "Id" need to be the same for every type of record, i think ur mast hope is to change it manually,
table "CountryA" (idcountry, population)
table "CountryB" (idcountry, surf)
if its the same country, "CountryA::idcountry" and "CountryB::idcountry" must be the same, that's the idea, so ur data needs to respect this critaria
I'm not sure that the relationships that I see in Manage Database match your verbal description here.
You mentaion a table AAAA. Your screen shows that the database file is named AAAA, but there's no box in your relationship graph named "AAAA".
You may also find that it works better to define your relationships so that each of the child tables: AAAC, Sale Record, Hygine, AGNT each link directly to your main (parent) table, instead of to each other (instead of Agent to AAAC, for example).
Is HdrArea a value that is imported from your CSV files or is it a field defined in FileMaker to supply the needed ID value? I would assume that the value is imported with the files. If so, as Kays has pointed out the matched key fields need to contain the same exact data. They also need to be defined as field's of the same exact data type. In FileMaker key fields, these are most often number fields, but they could also both be text fields, just make sure that you don't have HdrArea in one table defined as text and the match field in another table defined as number.
I think you understand now what FMP needs in order for it to know which records of one table should relate to which record(s) of another table.
Now to get there:
Do you still have the csv files? Are they up to date?
Before these csv records were imported into FMP, how did you know which address went with which property?
From this information, we may be able to suggest how to link together 630K records a bit more efficiently than manually searching through them all. Perhaps even how to easily get them all into one table with correct updating of the records concerned.
The key here is for us to understand what linking criteria is available...so again, how did you know BEFORE FMP which data went with which data? That will be the only key we have to work with here.
Yes sir, that is basically where I am at.
I do have the CVS files, they are all current and up to date. All of these records are horizontal, as long as the HdrAddr field matches between any or all of the different tables, then that information needs to be able to be viewed cumulatively.
Again. The AAAA table has all of the 630K records, each one has a uniquie ID. The other 4 tables have less records than the first, but the uniquie ID is present so that it can be attached to the larger data set if there is information present in the smaller tables.
It seems that I have just gone the wrong way abot importing these records into FMP, I sincerly hope that you can offer some guidance on the proper wat to address this.
Sorry, I didn't understand the answer to my question...let me ask it a different way...
In a single csv file, does each line contain ALL of the data for that particular record, and you are trying to split that line into multiple tables? Then the next csv file simply has more records having no relation to the previous csv file?
A single record is split among multiple csv files, and each csv file with info on that record has the same unique identifier as one of the values in that csv file?
Please note, as Phil pointed out, that you do not HAVE a AAAA table. You have an AAAA database with multiple tables inside it, none of which are named AAAA. Semantics, yes (sorry), but a pretty important distinction as we figure out how to get you where you want to be.
From the looks of things, as best I understand you needs so far, you are better off with a single table dbase for now. The only things that would make me suggest otherwise is if one Agent is referenced in multiple records, or multiple Xactions for one property, etc. For where we're starting...let's assume one table for now.
Which of the above two options describes your starting point? The paths from the two starting points would be slightly different. (Note that both would include wiping and reimporting the data...keep your csv's up to date!
OMJesus, I am an retard, When I originally imported these an extra character snuck its way into the HdrArea and messed up all the serial numbers in the primary table. I am sorry to have waisted any of your time.