What is the 'Good Way' to Do a Relationship ... and Avoid Duplicate Entries?

[Tony Diaz]

I've been a flat file creator forever, as the best part of the Relational database, to me was 'voodoo that I'll figure out later'.
Now that some more bits of using related fields makes sense to me, what is the proper way to do it?

I want to 'clean up' now before my file grows more.

Tables:

Records

Vendors

Publishers

In a database for collecting items, 'Records' is the main table.  Items come from vendors, or publishers, or .. (whatever)

I am also keeping a table of vendors, publishers and their data. But in the Records table I only want to display the vendor name. Vendors and Publishers are entered on the Records layout.

The tables each have unique keys.

Records::ID                    Vendors::VendorKey    Publishers::PublishersKey
Records::Item Name    Vendors::Name             Publishers::Name

The Records Layout has Records::Item Vendor on it.

The relationship that I currently have, which is probably 'wrong', is Records::Item Vendor <----[=]----> Vendors::Name.

I've added Vendors::Name to the Records Layout and filled them in. So now as I go forward if a Vendor already exists, I would like it to relate to that vendor record and not create a new one in the Vendors (or Publishers) table.

The field is setup to Autofill using existing values. Should I have this be a value list instead? (based on existing values)
Many of the blog posts go back and forth over Validation vs. Scripting. I don't need it to tell me if it's a duplicate. I want it to create the new vendor record if needed, or relate to an existing one.

 

[Wim Decorte]

Relationships should always be based on 'meaningless' keys.  So don't use names as the key.  What if a vendor changes their name?

Also your screenshot does not reflect your description when it comes to field names and key names.  For instance, you have a field name "publisher" in table Publishers.  What's in the field?  The name of the field should tell you what is in it.  Your future self and/or the developer that needs to pick up from you will be grateful.

Pick a naming standard and stick to it.  We use "ID" for all primary keys.

1 hour ago, Tony Diaz said:

The field is setup to Autofill using existing values. Should I have this be a value list instead?

That question doesn't have much to do with relationships but rather with the business workflow of creating records.  There's no good answer, pick what is intuitive to the user at the time the record gets created.  You can also use a card window showing a list of the actual records.

It's not that hard to make the vendor picking and publisher picking workflow so that you have the opportunity to check if something exists and prompt the user whether they want to create a new record.

 

1 hour ago, Tony Diaz said:

Many of the blog posts go back and forth over Validation vs. Scripting.

Yep.  If you do the validation at the schema level, it gets enforced always.  But it can get in the way if you want to do something in an automated way where you need to make an exception.  And you don't have much control over the messaging.

I tend to prefer to keep all my business logic at the scripting level.  Makes for easy troubleshooting and adjusting.  For instance I can change the validation rule in a script without incurring a schema lock that you'd have when you have to change a field definition (which leads to error 302 if someone tries to create a record while that is happening, which you then hope you have error trapped and handled,... which is why doing live development is not a good idea).

[Tony Diaz]

WRT Validation vs. Scripting. Got it, and yes, there may be times when I want to have an exception to the rule so, okay. Scripting that will be.

So, with that:

I'm not seeing something here.. something that is probably right in front of me.

So I set up the relationship with the auto generating ID in Vendors which is related to a field in Records

Vendors::ID <---[=]---> Items::Vendor_ID
[X] Create New
      Records

On the Items Layout, I have placed Vendors::Vendor and it's set to auto fill using existing values.

 

So then, what are the steps and proper layout bits to have it relate to an existing vendor in that table, or create the record of what I'm entering does not already exist. (...and I can tell that by seeing the Auto-Complete).

Everything I find as examples are setup to warn me that it's a duplicate entry about to be created.
I'm wanting to relate to a record over in the other table and create it if it does not already exist. I'm just not wrapping my head around how to get that to work.

I don't need it to warn me that I'm going to enter a duplicate. I don't even care if I don't know that it doesn't exist. But if there has to be a manual step in there, then so be it. Either just create the related vendor record, or relate to an existing one. 

It sounds like a One (Vendor) to Many (Items) relationship, so I have the ID auto-generated on the Vendors Table, and the related field in the Items Table is it's Foreign Key. But it creates a new record in the Vendors table each time.
 

[comment]

1 hour ago, Tony Diaz said:

Either just create the related vendor record, or relate to an existing one. 

Filemaker does not have a built-in mechanism for this. If you enable automatic creation of records on the Vendors side of the relationship and enter a value into (any) field of the Vendors table placed on the layout of Items, Filemaker will create a new record in Vendors every time - regardless of whether such value already exists. If you have validation in place, you will get an error when you try to commit such record, but in any case you will not get what you want.

If you really want it to work it that way, you would need to implement this yourself using a scripted process. For example, enter the vendor's name into a global field and perform a find in the Vendors table. If no records were found, create a new vendor record, get its  ID and use it to populate the foreign key in the Items table; otherwise grab the ID of the found record.

The problem with this method is that a user may enter a name similar to one that already exists, but with a spelling mistake. Or that more than one record is found. So the more common method is to allow the user to select from a list of existing vendors (optionally assisted by a find, or by a partial match) and have them select New… only when they can't find the vendor in the list.

 

 

 

Edited June 4, 2020 by comment

[Tony Diaz]

 

9 hours ago, comment said:

So the more common method is to allow the user to select from a list of existing vendors (optionally assisted by a find, or by a partial match) and have them select New… only when they can't find the vendor in the list.

Even this is acceptable, if I can get the functionality of it to relate to an existing record. Like I said, I can tell if the vendor does not exist because the auto-complete didn't happen during entry. So, I can click the "New Vendor" button/option and have it enter that.

Without the [X] create new record, I can't even select the Vendors::Vendor field on the Items layout. Is this the difference between Referencing data from a different table vs. Mirroring it?  I get the impression that having "duplicate" data is bad form. (e.g. a Vendor Name field in both Items -and- Vendors table but I also get the impression that it sometimes just needs to be done that way sometimes.

Now it may not be the most intuitive UI measure, but If I use existing values in Vendors::Vendor for a Value List then I can tell said Vendor already exists. Because it auto-completed what I wanted to enter.

This sounds like I need to have a field in the Items Table for the vendor as well, then, and either some kind of script on committing, that checks if it exists in Vendors::Vendor, and creates it if not.

Otherwise, what I'm getting from this is if I want to relate to an existing record in another table, I need to fetch the ID for that record myself, stick that in the foreign key field ...and then that field from Vendors::Vendor that is on the Items Layout will show the associated value, of that ID?

 

[Wim Decorte]

17 minutes ago, Tony Diaz said:

Without the [X] create new record, I can't even select the Vendors::Vendor field on the Items layout.

That is because there is no related record to click in.

By selecting the "auto create" you are telling FM: create me a related record.  Which is not what you want to have happen.

This is more of a UX/UI issue than anything else.  The workflow should start by presenting the user with a list of vendors, where they can search or create one.  When the select the vendor you store the ID in a variable, then go to the Items table and create a new record an populate the foreign key for the vendor.  That establishes the relationship between the item and the vendor.

Or perhaps the user selects from a list of existing inventory items and you already know who their vendor is.

 

[comment]

31 minutes ago, Tony Diaz said:

Otherwise, what I'm getting from this is if I want to relate to an existing record in another table, I need to fetch the ID for that record myself, stick that in the foreign key field ...and then that field from Vendors::Vendor that is on the Items Layout will show the associated value, of that ID?

Yes. There are several methods to fetch that ID - but the easiest one is to have the user click on the record shown in a portal or in a card window. You could also use a value list defined to use values from the ID field while displaying the name  - but then you won't get the type-ahead feature.

 

[Tony Diaz]

I can get what I want to work right now, almost:

I can tell if the Vendor does not already exist when I'm entering it.
I can create that record in Vendors::Vendor using the value that I've entered in Items:Item Vendor with a button.

What I'm not figuring out how to do is get is when I select that Vendor from the value list, to have Items::Vendor::ID be populated with Vendors::ID. (Which would then automatically show the related vendor)



..and bonus: To have the button hidden when the value of Vendors::Vendor equals a value in the value list "Vendors".
Something with not IsEmpty( FilterValues ( "Vendors" ; Items::Vendor) )

 

This way I have the option of using a Vendor from my list/table of Vendors, or using a One-Off entry. (..and that button will just stay visible as long as that value isn't added.)

Sorry for the madness, I really am learning from this, it's just this particular work flow is slightly different.

Think "Link this order to an existing customer" vs. a one off "Cash Sale to Fred Flintstone"  (and with the button I have the ability to create a customer called "Cash Sale to Fred Flintstone" if I want, because that is what is entered in the field.

 

Edited June 5, 2020 by Tony Diaz

[comment]

53 minutes ago, Tony Diaz said:

What I'm not figuring out how to do is get is when I select that Vendor from the value list, to have Items::Vendor::ID be populated with Vendors::ID

You could define another relationship to the Vendors table matching on name:

Items::VendorName = Vendors 2::Name

Then let the Items::VendorID field lookup the value from Vendors 2::ID.

This is a bit convoluted and may require additional adjustments for cases where no match exists, but it will do what you describe. Another option is to script this (as I already described).

 

Edited June 5, 2020 by comment

[Tony Diaz]

I'm not figuring out where / how to script this

On 6/4/2020 at 5:28 AM, comment said:

If you really want it to work it that way, you would need to implement this yourself using a scripted process. For example, enter the vendor's name into a global field and perform a find in the Vendors table. If no records were found, create a new vendor record, get its  ID and use it to populate the foreign key in the Items table; otherwise grab the ID of the found record.

I've got the global field set to use a Value List from Vendors that lists them by name, but will return the vendor ID when a selection is made.

I'm presuming that I'll need to do a script trigger on exit of that field ... that takes the result, if there is a vendor ID returned, it places it in Items::Vendor_IDfk, and if there is no ID returned when exiting the field populate a different field with the value from that global field?

In the end I either end up with a value in Items::Vendor or Items::Vendor_IDfk. The former is no related record, the latter would be a related record and then I have the initial search field hide because I've got values in one/or the other.

So the question is, where do I trigger such a script from and what will it look like? 

[Wim Decorte]

Again: UX workflow.  Sketch out the workflow, perhaps use a flowchart if that works well for you.

I wouldn't use a value list for the vendor picking.

When it is time for the user to pick a vendor I would show a card window, with a built in search functionality and a button to create a new vendor if the user can't find what they are looking for (that process is its own workflow).

When the user selects a vendor from the list shown in that card window, that is done by a button that runs a script to grab the vendor ID, close the card window, locate the right item record and sets the vendor ID into the item's vendor foreign key field.

 

[bcooney]

Here’s a thread on pickers. I posted a demo file.