Relationship Inquiry

[vwgtiturbo]

Hello all,

While I'm not exactly new to using FileMaker (17, in this case), I am quite wet behind the ears with a lot of its functionality, and the proper way to do things. I've put together a few solutions that seem to work, but they aren't pretty, and I'm sure they are quite inefficient.

I'm not really sure how to ask this question, so I'll lead with a simplified example:

Let's say I have one table that is the focal point of a relationship (Table X). In that table, I store foreign keys to smaller tables (1-5) that just provide options for the main table foreign key fields (via value lists based on the 1-5 table fields).

Is it necessary to connect each foreign key field in Table X to its respective foreign table (1-5), or would I only need to connect Table X to Tables (1-5) for the relationships that I'm trying to preserve/view related data on?

I'm asking this as I have a solution that contains only 2 or 3 main relationships that I'm connecting the data with (across 50+ tables), and I have a bad habit of connecting EVERY fk_field to its respective "1-5" table. Naturally, this looks like a mess, with many different table occurrences being added to the graph, and greatly complicates value list generation, calculation fields, and report generation. In the end, I have to wonder if it's even necessary, if I am not viewing, or concerned with any relationships outside of the 2 or 3. It seems more... Efficient, if i don't connect each FK field, then simply create a two field value list of the item in question (using the "1-5" table id and value fields), then on the layout, make the Table X field reference the value list (using a pop up menu instead of exit box, that way it will show the value of the field instead of the foreign key).

This comes up while I'm attempting to restructure a monster project to make it maintainable after I leave my current position. The original project is just... Unwieldy, and while my solutions work, I sometimes think it's by accident/trial and error.

Thanks in advance for any insight. I hope I made sense in the question; it's one of those things that makes perfect sense in your head, but...

[comment]

This is difficult, if not impossible, to answer in abstract. In general, you use a field-based value list  because (1) you want to store the value's attributes in a single place or (2) you want to be able to rename the value without having to replace it in every record where it has been selected or (3) you want users to be able to add values to the value list. If these concerns do not apply to your situation, then you should probably be using a custom value list. 

 

[vwgtiturbo]

On 9/11/2020 at 2:17 PM, comment said:

This is difficult, if not impossible, to answer in abstract. In general, you use a field-based value list  because (1) you want to store the value's attributes in a single place or (2) you want to be able to rename the value without having to replace it in every record where it has been selected or (3) you want users to be able to add values to the value list. If these concerns do not apply to your situation, then you should probably be using a custom value list. 

 

Hello! Thank you for the response (and my apologies for my lack of it!). I spent a couple of days going over what you stated, initially thinking "Hmm... that doesn't address the relationship angle of my question??". However, after a few days, I am starting to understand the point (and realizing that I need to find some good resources for designing with FileMaker, versus the multitude of resources that simply explain how to use the software).

When designing how things were going to go, I was only thinking about designing it in a way that would translate to a "normal" SQL database (I should say, how I THINK a normal SQL database would be; value lists without tables are a FileMaker thing... right??). That way, when I am gone, someone could look at the design data and simply recreate it in MySQL/Postgres, etc.. OR if there was something in the list that needed to be changed/added, the layouts are already in place to allow for that.

However, FileMaker's use of table occurrences complicates things (I really need to find a resource to grasp the hows and whys of it; it might clear up my database if I could build it with intention, versus trial and error that just seems to work).

Anyway, beyond my needing to work on my table occurrence confusion, I am going go give the 'value list' idea some serious consideration. Out of my tables, I'm thinking that at least 50% are used simply for values/choices in which I am not trying to maintain a relationship (and therefore, a custom value list may very well be the best option). That would certainly cut down on my tables/TOs.

[bcooney]

Let’s take a product table as an example. One attribute is size. You could use a custom value list of hard coded choices: small, medium, large. However, if you decide that you’d rather have the choices : S, M, L, then you’d need to change existing data. Really, not a big deal. 

You can avoid this situation by using a value list based on a table. The user selects the Id from the size table. Now if you change the size choices, there’s no data conversion since the relationship is based on Id. 
 

Do you need to have a relationship from the parts table to the size table? No.  As you see, simply assigning the value list to the pop up menu on the product::SizeId Field is all you need.  You can hide the pop up menu icon on a print report. 

[vwgtiturbo]

On 9/13/2020 at 8:56 AM, bcooney said:

Let’s take a product table as an example. One attribute is size. You could use a custom value list of hard coded choices: small, medium, large. However, if you decide that you’d rather have the choices : S, M, L, then you’d need to change existing data. Really, not a big deal. 

You can avoid this situation by using a value list based on a table. The user selects the Id from the size table. Now if you change the size choices, there’s no data conversion since the relationship is based on Id. 
 

Do you need to have a relationship from the parts table to the size table? No.  As you see, simply assigning the value list to the pop up menu on the product::SizeId Field is all you need.  You can hide the pop up menu icon on a print report. 

Thank you very much for that example. It perfectly explains what I was looking for (and the idea really simplifies my relationship graph).

I did have to create some relationships to allow conditional formatting (that is based on field value) but otherwise, things are much more tidy.

 

Thanks again for the confirmation!