Table Relationships and Value Lists?

[Tony Diaz]

I think I'm in over my head. I drew this out on paper, but I can't get this to work.

 

I'm trying to emulate the behavior below.

The column on the right, "Genre, Gameplay, Prospective" ..  to where I pick the item by name "Action, Arcade, Chess" etc in the first column of the portal, and it shows the associated sub category next to it.  The choice would be entered by either by Genre_ID or Genre_Name and then the genre_category_id & genre_category from the same record would show with it. The unique value being 'genre_ID', and it's related to 'Game_ID' on the main table.

Game_ID is a unique value, but it's not -the- unique identifier for the record which is Moby_ID (UUID)

The portal will be on a different tab, but I'd like the first item / category to also show on the main layout with the game record.

The value list  / names / ID's come from  a table that is being used to load the value list.

The idea being that if I were to change one of them title/description wise, all the records would change.

As in say, change "Arcade" to "Arcade Game".  So that's why I want to keep the titles in

 

   

Games:                                         multiple records per game               The List of names.

Sandbox_2.fmp12

[Wim Decorte]

Looks like you didn't finish your write-up, so explain what is not working and we can focus on that.

One quick thought: your Moby_genres_items feels like a join table between Games and Genres. But it should be liked to Games through its the Games fk and to Genres through its Genres fk.  The way you have it it uses the same fk for both connections and that can't be correct.

[Tony Diaz]

That's just it., and whats not working. That's how I drew it on paper, using the fk in Games, but that doesn't seem like it would provide a unique link identifier.

The Games table is the one that has the ultimate unique identifier.

So I changed it that which seems to push the unique value over to the that.. which yes, seems to work except I'm not sure why. Nor am I sure how to create a "unique" value for that .. so shMaybe that's the part about the relationships I'm still not understanding. I thought both values needed to match.
(and they do, but I'm still not sure though, why that works)

I still get the same value over and over in the portal even though I've entered different data into the actual row. Plus if I choose a new value it does not update the current portal row.

It's a little closer otherwise.

Plus Join Tables still isn't clear to me, but it appears that as you say, that's exactly what I'm doing.

 

 

 

Sandbox_2.fmp12

[Wim Decorte]

13 hours ago, Tony Diaz said:

but that doesn't seem like it would provide a unique link identifier.

and

13 hours ago, Tony Diaz said:

The Games table is the one that has the ultimate unique identifier.

 

I think these two sentences reveal some flaw in your thinking about relationships.  What is that elusive "ultimate unique identifier" that apparently supersedes tables (based on your description)?

Here's how you normally would approach this: you have two big entities, Games and Genres.  That means two tables, each table has a primary key (a unique identifier) and attributes (fields) that describe that entity.

For Genre that is a genre name and description.

For games that is the game's name, release date, ... and a genre.  For each of those though you have to think: how many of these can a game have?  Can a game have multiple genres?  Can a game have multiple release dates (one per country perhaps).  Say that a game definitely can have multiple genres, now you have a one-to-many relationship between games and genres.  Which means you'll need a new entity: "Genres assigned to games". So a new table.  Like all tables it gets it own unique primary key.  And it has a foreign key to link it to the game that this genre is assigned to.

But of course, as you stated earlier; you don't want to assign Genres to a game by typing in the Genre name; you want it to link to the actual Genre table so that if you change a Genre name, it will apply to all games where that Genre was picked for.

And each Genre can be assigned to many games.  We already know that each game can have many genres.  So what we have here is a many-to-many relationship between the Genre and game tables.

Fortunately our "Genres assigned to games" table works just fine for that.  Just add one more field: the foreign key for the picked Genre.

The relationship then looks like this, using your file:

 

In your original "Moby_Genres_items" table you had the ID field defined as a lookup.  Why?

An ID field in any table typically denotes the primary key and should be set to something that will be unique, a serial number or a UUID text.

Edited June 27, 2020 by Wim Decorte

[Tony Diaz]

23 hours ago, Wim Decorte said:

In your original "Moby_Genres_items" table you had the ID field defined as a lookup.  Why?

Probably left over clickery from scrambling around with it.. that didn't work 😉

After messing around with this one more, I went back to the previous boilerplate file where I had gotten it to resemble what I was trying to do a bit closer.. and at least now I know how I got to that point with it.

 

I can pick one or more of those "sub-category" genre types and only see the related choices to it as well as select multiple genres from the sub-category.

 

 

What I was trying to get to with this thread is to have later edits of the Value List items reflect in the existing Items records where these choices have been toggled.

e.g. If I were to change "Basic Genres" to "Genre Types" and have it reflect that on all the existing stored items.
 

I can either get the workflow and display to do what I want but not be able to reflect the current values in the Value List, or I can make it reflect them, but then I have to see all the selections, all of the time and see the sub-category repeated.

 

Forgive me for seeming to be jumping between objectives, it wasn't quite sure how to word what I wanted perhaps.

So, with the attached fmp file, which is a prior version of this, I gather that if I want to make a global change to the value list -and- I want to reflect that change in all the existing records, I'm going to have to script it to apply those changes?

 

 

 

Sandbox.fmp12

[comment]

Déjà vu.

1 hour ago, Tony Diaz said:

 if I want to make a global change to the value list -and- I want to reflect that change in all the existing records, I'm going to have to script it to apply those changes?

The proper method is to use a meaningless ID as the match field for the relationship. Then you can change the name of a parent record and have it reflected in all its children, because the name is stored only in the parent record.

However, you wanted to use the value list for auto-completing the parent name - something a value list using values from an ID field cannot do. This was discussed here and I suggested a workaround. The side-effect of the workaround is that that the name of the parent is also stored in the child record.  So if you change the name of the parent, you also need to propagate the change to its children.

This complexity could be avoided by using another method to select the parent - also discussed in the other thread. 

 

Edited June 28, 2020 by comment

[Wim Decorte]

1 hour ago, Tony Diaz said:

Forgive me for seeming to be jumping between objectives

It's getting in your way.

You're struggling with two things:

1) set up the proper fundamental relational architecture between your core entities.

2) set up a workflow for the user to pick from a 'dwindling value list', to create related data

And you are trying to solve both at the same time.  They're not related.  Set up the fundamental architecture first.  Along the lines of what I explained in this thread.  Only then, turn to the second problem.  Which you do NOT solve by fiddling with your fundamental architecture, setting up a dwindling value list is its own problem with its own relational structure to make the technique work.