Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Create Related Record makes duplicates


jelockwood
 Share

Recommended Posts

Our company is still mainly using FileMaker 6 but will hopefully soon move to FileMaker 9 or 10.

 

I am currently experimenting with FileMaker 9, but puzzled by an issue with creating related records. For this test I have a single database file with two tables, the two tables are linked via a relationship via a match field and I have ticked the option to allow the creation of related records in the second table.

 

Table1

"Project"

"Date"

"Hours worked"

 

Table2

"Project"

"Requester"

"Authoriser"

 

The two "Project" fields are the match fields used for the relationship. If I create a record in Table1 and enter a new Project number it does indeed create a new matching record in Table2, however if I create another record in Table1 with a project number that has already been used, it creates another new record with that number in Table2, this results in lots of duplicates in Table2.

 

Ultimately the aim is to display fields in the layout of Table1 pulling data via the relationship from Table2. In Table1 there would be multiple records using the same project number but in Table2 each project number should only exist once.

Link to comment
Share on other sites

The two "Project" fields are the match fields used for the relationship. If I create a record in Table1 and enter a new Project number it does indeed create a new matching record in Table2, however if I create another record in Table1 with a project number that has already been used, it creates another new record with that number in Table2, this results in lots of duplicates in Table2.

 

Even with "allow record creation via this relationship" ticked (presumably in the Table 2 side?), merely creating a record in Table 1 should not cause a corresponding record in Table 2 to spontaneously spring into existence. "Allow record creation" would come into play if and when you do a Set Field of a Table 2 value when there is, as of yet, no corresponding related Table 2 record in which to set such a field value. Thus if you create a new Table 1 record and then do this while still operating from the Table 1 layout's vantage point:

 

Set Field [Table 2::Requester; Get(AccountName)]

 

...that should cause a new Table 2 record, related to the Table 1 record that you are on, to be spawned.

 

 

 

To say specifically what you are doing wrong so as to cause Table 2 records to be created when there are already related Table 2 records, I'd have to know more specifically what you are doing. Can you screenshot or type out your script, exactly? (Or if it is not via script but via portal or something, go into careful detail?)

 

My guess is that you are somehow switching to a Table 2 environment (layout) and then creating a record, which it will indeed do even if there is already a record corresponding to the Table 1 record that you left to get there.

Link to comment
Share on other sites

The two "Project" fields are the match fields used for the relationship. If I create a record in Table1 and enter a new Project number it does indeed create a new matching record in Table2, however if I create another record in Table1 with a project number that has already been used, it creates another new record with that number in Table2, this results in lots of duplicates in Table2.

 

What are your actual tables for? What is Table1 and what is Table2?

 

If I read this correctly, 1 project only has 1 "Requester" and "Authoriser", but can have many dates on which hours of work were performed. I assume you have the related Table2::Project field (or a portal to Table2) on the Table1 layout. And are using that to type in the Project.

 

If yes, that is your problem. That setup tells FM to "Create a new record" in table 2...ignoring the fact that there might already be a project with that name/id.

 

You can do it 1 of 2 ways (or both if you wish)

 

  1. From the Table1 Layout - Create a new record and enter in the project from there into the Table1::Project field, not the Table2::Project field. (no "Allow creation of records via this relationship" on the Table2 side).
  2. From the Table2 Layout - use a portal that looks at Table1, with the "Allow Creation of Records via this relationship" on the Table1 side of the relationship. Then simply enter the date and hours into the appropriate fields. FM will automatically fill in the Project field for you, whether showing or not.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use