create a relationship between the 2 tables using the matching field criteria (not the foreign key to primary key), lets call these 2 table occurrences Parent1 and Child1 for now. Then run a script like so:
go to layout (any layout based on Child1 table occurrence)
show all records
replace field contents (target = the foreign key field in the child table, calculated value = Parent1::primarykeyfield)
alternatively you can use executeSQL in your replace field contents calculation instead of creating the relationship
Hi, I created a relationship between the tables on the Activity field and not the ID field. Then tried to replace contents with calculated value but displays 0 for all records. How can I make it look for a match between the text column that both the tables share?
The relationship you describe should do it. Either the field's data types are not correct of the values in those fields of the two tables do not exactly match.
Thanks, The 2 text columns are text and the others are numeric. If that is correct then this must be incorrect as the values definitely match
Company::Activity = Activity::Activity = Activity::ActivityID
If they are text fields, all it takes is a difference of one character--even an invisible space in the value of one of these fields to create a situation where it looks like the values should match but in reality, they don't.
What you have posted for match fields seems to describe an impossible relationship. You can't have two table occurrence boxes with exactly the same name so
Activity::Activity = Activity::ActivityID
makes no sense.
Can you upload a screen shot of this portion of your relationship graph in order to clarify?
I've triple checked everything listed here and it just displays 0s - is there anyone else that can help?
Why would you expect renaming a field to change how this works? If you were using a different database, would you expect renaming a field to change how a relationship works?
What I was noting was that your field names implied that you were not matching the same kind of data in your match fields.
You posted earlier:
Company::Activity = Activity::Activity = Activity::ActivityID
What I find in your screen shot (that could have been uploaded with the controls found below the Post A New Answer box):
Activity::ActivityName = Company::Activity
Which is more than a name change as Activity::Activity = Activity::ActivityID is not part of any relationship in your database at this time.
From your first post, I gather that you consider Activity to be your parent table and thus company is the child table? Is it possible that you have the need to link many companies to the same activity and a given activity to more than one company? That would require a different relationship than what you currently have in place. But let's leave that issue be for a moment.
I would suggest that you set up a portal to companies on your activities layout and see what records appear in that portal.
it just displays 0s
What is "it"? Is this a field? A field in what table? Or are you looking at information in the upper left corner of the status area tool bar?
is there anyone else that can help?
If you prefer, I can make this my last response to your questions. But I have been working with FileMaker since it was FileMaker Pro 2.5. I generally am able to provide good advice here so I really don't think stopping now is likely to be helpful to you.
From your first post, I gather that you consider Activity to be your parent table and thus company is the child table? Is it possible that you have the need to link many companies to the same activity and a given activity to more than one company?
Yes that is what I need to achieve. My apologies for not being clearer and I do clearly need your advice. What I mean is the field ActivityID in the Company table after specifying the calculation displays 0s instead of what should be 2 (Dairy Products) and 4 (Special Foods & Wellbeing Products) This is when in browse mode.
When adding a portal in layout mode nothing seems to be displayed..
And what calculation is that? Can you identify the field and paste it's calculation here so that I can see it?
Neither Company::Activity nor Activity::ActivityID should be calculation fields. They should either both be number fields where Activity::ActivityID auto-enters a serial number or both should be text fields where Activity::ActivityID auto-enters the calculation: Get ( UUID ). (Note that adding either feature to a table that already has records will not automatically assign values to existing records. They'd need to be updated using Replace Field Contents to given them either serial numbers or UUID text.)
the need to link many companies to the same activity and a given activity to more than one company
This describes a "many to many" relationship and is almost always implemented by setting up a third "join" table that links Company to Activities. But I don't think we want to go there until the above confusion is cleared up. The original set up describes a "one to many" relationship that is basic to any relational database setup and is "step one" to setting up a many to many relationship so we need to get that working first.
Hi, OK it is necessary for me to add these numbers to an existing table as I have split up a large excel spreadsheet and imported into different tables in filemaker. Therefore I have to match the activity in the company table with the corresponding ActivityID in the activity table, and by that I mean filemaker needs to match up the the activity from both tables (which are text fields) and generate the corresponding ActivityID number for that record.
(Note that adding either feature to a table that already has records will not automatically assign values to existing records. They'd need to be updated using Replace Field Contents to given them either serial numbers or UUID text.
In the Company Table, I have been selecting a record in the ActivityID, going into replace field contents, replace with calculated result, and this is where I am stuck. From your answer it seems what I've been trying to do isn't possible maybe? The Get UUID doesn't seem like it would be right here as it generates a unique number for each row and I need the corresponding ActivityID for for that record to be present in the company table.
Sometimes trying to help someone via the forum is like peeking through a house's front door keyhole and trying to figure out what color paint was used in the bathroom. My memory may be defective here and I'm not taking the time to re-read previous posts, but I don't recall any mention that you are trying to link data that has been imported from an external source instead of creating new record in a related table.
Therefore I have to match the activity in the company table with the corresponding ActivityID in the activity table, and by that I mean filemaker needs to match up the the activity from both tables (which are text fields) and generate the corresponding ActivityID number for that record.
As I read that, you have TWO different pairs of values. You have text fields with a name or description of an activity and you have ID fields, typically defined as number fields that match records by ID. When importing data, it's often necessary to match records by a "name" field temporarily in order to assign ID numbers so that you can from that point forward link the records by IDs rather than names.
Is that what you are trying to do here?
If so, set up this relationship:
Companies::ActivityName = Activities::ActivityName
This assumes that your import from Excel put exactly the same text into these two ActivityName fields. If this part works, You should be able to put a portal to Companies on your Activities layout and see at least every record from company with a matching name. Make sure that works before moving forward.
The next step is to set up matching ID's in the two fields. Start with Activities. If you have a defined ID field with a number or auto-entered UUID and you can see that value in each field, great. If not, Add such a field and use Replace Field Contents to populate that new field with values. Then go to the companies layout, put the cursor into the Companies::ActivityID field and use replace field contents with this calculation:
This copies over the ID from the matching Activities record. With that value finally in place, you can change the original relationship to be:
Companies::ActivityID = Activities::ActivityID
The join table may take some added work Here's how a join table would work between activities and companies:
Activities::__pkActivityID = Activity_Company::_fkActivityID
Companies::__pkCompanyID = Activity_Company::_fkCompanyID
You can place a portal to Activity_Company on the Activities layout to list and select Companies records for each given Activities record. Fields from Companies can be included in the Portal to show additional info about each selected Companies record and the _fkCompanyID field can be set up with a value list for selecting Companies records by their ID field.
So the trick will be to populate the join table with the correct IDs. And note that the join table is often a good layout context for reporting as it can list data from both the other two tables as needed.