Jump to content

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


Recommended Posts

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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). 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.