Help Prevent Duplicate Child Records in Relation to its Parent table.

[dmontano]

Hi all,

I need to prevent a child table from creating a record when the value already exist ONLY in relation to its parent table.

Table = Party (Parent)

• Party ID 1
• Party ID 2

Table = Party Names (Child)

• Party ID 1 
  • Party Name ID 22 = John Doe
  • Party Name ID 23 = Johnny
  • Party Name ID 24 = John Doe (This is what I want to prevent)
• Party ID 2
  • Party Name ID 25 = Sally Sue
  • Party Name ID 26 = Sally
  • Party Name ID 27 = Sally Jane Sue
  • Party Name ID 28 = Sally (This is what I want to prevent)

My current solution:

1. A text field with an auto-enter calculation on the "party_name" field in the PARTY NAME table defined as: "Trim(self)". This solves the case when a name is entered twice. 
2. A calculation field in the PARTY NAME table defined as a concatenation: "party_fk & " - " & party_name). I am joining the PARTY foreign key to the PARTY NAME text value.
3. A text field with an auto-enter calculation in the PARTY NAME table that auto-enters the calculation field in #2 so I can use the built-in Filemaker validation checkbox for only allow "Unique".

My Questions:

1. My current solution works, but I'm not sure if this is reliable, durable, and acceptable practice: is it?
2. Is there another way that this can be accomplished without the creation of these two extra "helper" fields?
3. Is this method "standard" practice; or is there a more durable, foolproof method.
4. What method have you used that has worked well for you?

Thanks in advance, David

[comment]

You need one extra "helper" field: a Text field that auto-enters a calculated value =

party_fk & "|" & party_name

Validate this field as unique.

 

[dmontano]

Hi Comment,

This should be what I have in "My current solution" as shown in line #3. 

Or, are you suggesting adding another field as you defined?

Also, the calculation you supply is using the pipe symbol "|": is there something I should be aware of between the symbol I used, hypen " - ", versus what you have supplied in your calculation?

Thanks!

[comment]

Just now, dmontano said:

This should be what I have in "My current solution" as shown in line #3. 

True, but you are using an interim calculation field to perform the concatenation. That's an unnecessary complication. Eliminate the middleman and make the text field do the work.

 

4 minutes ago, dmontano said:

is there something I should be aware of between the symbol I used, hypen " - ", versus what you have supplied in your calculation?

It probably won't make a difference in your situation, but best practice rules out any character that could appear in any one of the fields being concatenated. Hyphens often appear in names, so... Ideally, you would use a special character such as Char(31). 

 

[dmontano]

Now I understand:

I interpreted the portion of your comment "extra" as "in addition" to what I presented...

28 minutes ago, comment said:

You need one extra "helper" field: a Text field that auto-enters a calculated value =

...but I see I am the one who stated the "extra" fields to begin with: my bad.

As for the pipe versus hypen, makes good sense.

Thank you