dmontano Posted July 14, 2022 Share Posted July 14, 2022 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: 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. 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. 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: My current solution works, but I'm not sure if this is reliable, durable, and acceptable practice: is it? Is there another way that this can be accomplished without the creation of these two extra "helper" fields? Is this method "standard" practice; or is there a more durable, foolproof method. What method have you used that has worked well for you? Thanks in advance, David Link to comment Share on other sites More sharing options...
comment Posted July 14, 2022 Share Posted July 14, 2022 You need one extra "helper" field: a Text field that auto-enters a calculated value = party_fk & "|" & party_name Validate this field as unique. Link to comment Share on other sites More sharing options...
dmontano Posted July 14, 2022 Author Share Posted July 14, 2022 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 More sharing options...
comment Posted July 14, 2022 Share Posted July 14, 2022 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 More sharing options...
dmontano Posted July 14, 2022 Author Share Posted July 14, 2022 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now