Mission impossible... One-Many-One relationship design problem

[Buckie]

Hi fellow logicians! I hit a brain wall here. I'm possibly just dumb, but I can't wrap my head around the ways to solve this problem.

Consider the following scenario where we have three tables:

Table 1 has records with a number field that contains arbitrary integer numbers, from zero to some unspecified number.

Table 2 has (for example) two records in which we specify ranges: record#1 would contain (in two separate fields, "start" and "end") 10 and 20, and record#2 would have 15 and 90 in their respective numeric fields.

Now Table 3 has many records related to both at once: it would contain a unique identifier from a record in Table 1 and an identifier from Table 2.

The idea is to present a portal row with conditional formatting in Table 1 that would

1. Show all the associated records from Table 2 if its number field falls within the ranges of records from that table. That is, if a record in Table 1 has "18" in the field, the portal would list two records from Table 2, if it has "10" in that field, only one record will be visible and if it has, for example, "5" or "106", none would be visible. That part is easy, but...

2. Show related records from Table 3. If Table 3 has a record with both identifiers in place, this should be marked accordingly in the portal in Table 1. For example, with a checkbox.

You can view it as "enabled/disabled" scenario: by default we can see if records from Table 1 belong to a certain class, fall into some range by using a simple relationship. However we also need to know if that range has been "enabled" for a particular record in Table 1. Each record in Table 1, even if all of them belong to a certain range would have unique "enabled" flags for each range. That's the part that I cannot figure out the solution for.

There are workarounds for specific cases, for example, if numbers in Table 1 are unique then it's possible to create a one-to-one relationship but it's not an elegant solution when the ranges are very large. So... looking for some clues here.

[comment]

Why don't you explain the real problem behind this, instead of an abstract riddle that's impossible to follow.

Note: a table that contains arbitrary integer numbers, from zero to some unspecified number, does not represent anything in real life and accomplishes nothing except waste of resources. The only real information it holds is the value of the last record - assuming it has any significance, which I suspect it doesn't.

[Buckie]

Well, riddles have their place too, is that not true?

The real world application would be controlling the execution of service bulletins on certain VIN numbers. Suppose VW issues a service bulletin for cars with VIN numbers in a certain range and we would like to track down the particular cars that have had the service and those that have yet to receive it. And there may be not just one but several bulletins all concerning different ranges of VIN numbers.

So we already have a database of vehicles and their VIN numbers. And we have the information on bulletins with ranges. The question is how to create a portal in a vehicle's record/form that would not only list the service bulletins pertaining to it but would also allow an operator to tick a checkbox to mark a certain service bulletin for a particular vehicle as completed.

[comment]

Well, riddles have their place too, is that not true?

Sure they do. But that place is not here...

 

So we already have a database of vehicles and their VIN numbers. And we have the information on bulletins with ranges. The question is how to create a portal in a vehicle's record/form that would not only list the service bulletins pertaining to it but would also allow an operator to tick a checkbox to mark a certain service bulletin for a particular vehicle as completed.

Those are two very different requirements. To show the vehicles to which a bulletin applies, you would have to have three tables: Bulletins, Ranges and Vehicles, related as:

Bulletins::BulletinID = Ranges::BulletinID

and:

Ranges::RangeStart ≤ Vehicles::VIN
AND
Ranges::RangeEnd ≥ Vehicles::VIN

So when you get a bulletin with VIN ranges (I assume a bulletin can have multiple ranges), you need to enter the ranges into a portal to Ranges. Once you do that, a portal to Vehicles will show all the vehicles that apply. However, all the vehicles that apply will be shown alike, with no room to indicate which ones were processed. Similarly, a portal to Bulletins, placed on a layout of Vehicles, will show all the bulletins that apply to the vehicle - but again, there's no room to record the performance of the specified procedure on the individual vehicle.

In order to mark the processed cars you need to add another relationship between Bulletins and Vehicles - a many-to-many relationship, using a join table:

Bulletins::BulletinID = Recalls::BulletinID

and:

Recalls::VIN = Vehicles::VIN

Once you have that, the operator can tick a (fake) checkbox in the portal showing the applicable bulletins, and have a script grab the clicked bulletin's ID and the current record's VIN and create a corresponding join record in the Recalls table.

 

Edited October 10, 2015 by comment

[Buckie]

Comment, I'm sorry I wasn't clear enough and didn't parse all of the riddle into the real-life scenario. The hard thing here is how to have the portal display all the ticked checkboxes, not just allowing our operator to create a record. Creating is easy, but is it possible to see all the completed bulletins within the portal that lists them in vehicle's form?

I know how to do that with scripts, but that's an awkward way. The simplest way would probably be running a script on each record load in the Vehicles form to populate some fields after a couple of searches. That's doable of course, but it slows everything down and I wondered if there was a pure relationship approach that I somehow missed.

Another way would be to create thousands of separate records for the bulletins, turning ranges into records. That is, if we have a range from 0 to 10000, that would mean creating 10000 records.

Yet another way would be controlling when a new vehicle (and its VIN) is added to the table and populate the bulletin table accordingly with a script (creating new entries with that new VIN by checking if the newly created VIN falls within certain ranges). But those are just workarounds.

I feel like I'm missing some piece of a puzzle here, there has to be some way...

[Wim Decorte]

The hard thing here is how to have the portal display all the ticked checkboxes, not just allowing our operator to create a record. Creating is easy, but is it possible to see all the completed bulletins within the portal that lists them in vehicle's form?

So you are looking at a vehicle detail and there is a portal that should show all the completed bulletins for that vehicle?

Then yes; if your relational structure for your solution is correct, that data will show easily without running scripts to do searches.  That info would be straight from the join table between vehicle and the bulletin ranges table.  The "completeness" is just a status on that join table record.

How does your relational graph look at this point, after Comment's advice?

 

[Buckie]

Here's how it looks like now. I'm probably just being dumb as always.

[comment]

As I said earlier, the problem is where to record the performance of a specific bulletin on a specific vehicle. Without a join Recalls table, the only way would be to add a text field to the Vehicles table, listing the completed bulletins (or a field in the Bulletins table, listing the completed vehicles) as a return-delimited list. This is quite easy to set up: you only need to define a value list of ApplicableBulletins, showing only related values from Bulletins, starting from Vehicles, and use it as checkboxes for that text field.

The problem with this approach is that you cannot record anything specific about the performance, such as a date and who performed it. This is why I believe adding the join table is worthwhile. But yes, it requires an extra step of creating the join record. As you note, you could create these in advance - but I  don't see why you would need to; the time to create a record is when you have something to record in it - and in this case that's the time a bulletin has been applied to a specific vehicle.

Re your RG: I would place the Vehicles table where you now have Bulletins, and use two TOs of Bulletins instead. That way you can utilize both relationship from the one and the same layout of Vehicles.

[Buckie]

...like so? or am I missing something again?

It's because I did that already before, and it allows me to see all recalls from the POV of a vehicle and it allows me to see all bulletins, but how exactly can I populate that portal row of bulletins with the relevant info from Recalls table without scripting? Is that even possible?

It's like if I could transparently transfer a relationship from Ranges::Bulletin ID to Recalls for each portal row displayed in Vehicles, that would solve the problem. But that's impossible.

[comment]

...like so?

Yes, exactly.

 

but how exactly can I populate that portal row of bulletins with the relevant info from Recalls table without scripting? Is that even possible?

I think you mean "can I populate that portal row of Recalls with the relevant info from Bulletins table without scripting?" The answer is no, it is not possible. Why is this an issue? Script-assisted data entry is often beneficiary.

[Buckie]

Yes, exactly.

 

I think you mean "can I populate that portal row of Recalls with the relevant info from Bulletins table without scripting?" The answer is no, it is not possible. Why is this an issue? Script-assisted data entry is often beneficiary.

It's probably the same, but I imagined a form with vehicle records as a starting point, inside this form a portal with bulletins and in every portal row a field with status from recalls. So in that way we're populating a portal row of bulletins with data from recalls.

Out of curiosity, when would script-assisted data entry would be beneficiary? I usually shy away from it and create a boatload of relationships instead. Can you describe the situations when it would be better to use scripting instead of relationships? I am aware of the recent noSQL movement that basically relegates everything to scripts.

In my case I'd probably use a search/populate script then on each Vehicle record load.

Or maybe going the join table route is better, but it adds an additional layer that you have to maintain...

[comment]

It's probably the same, but I imagined a form with vehicle records as a starting point, inside this form a portal with bulletins and in every portal row a field with status from recalls.

In terms of user interface, there's no reason why you could not have this. The only difference is that the status from Recalls would not be a field, but some conditionally formatted object. And clicking on it would toggle it by way of scripting, instead of modifying a checkbox field directly.

 

So in that way we're populating a portal row of bulletins with data from recalls.

That's not possible, because that portal row represents either the entire bulletin or the entire range. Any changes you make to that record will affect all vehicles related to that bulletin/range, not just the current one.

 

Can you describe the situations when it would be better to use scripting instead of relationships?

Script-assisted data entry does not mean using scripts instead of relationships. A trivial example: instead of selecting from a drop-down menu, present the user with a pop-up window showing a list view of the records to select from. Another example, more close to your situation: in a POS station, create the invoice's line items by selecting the purchased items from a portal showing all items.

[Buckie]

Hmm... I wonder if it's possible to make a directional relationship. Not in FileMaker, but anywhere at all. That is, when we create a relationship between Table 1 and Table 2 but the results are different depending on which context we view them from. Table 1::ID == Table 2::ID but Table2::Color == Table1::Allcolors. So a "portal" in Table 1 would show all records with the same ID from Table 2, but a portal in Table 2 would only show entries with similar colors from Table 1 and nothing else.

Something like that without any special tricks.

Nah, that's something crazy I just said. Of course it can be done by using multiple TOs... I'm just trying to fix the impossible, hard.

Yeah, the illogical presumption behind my attempts starts to make sense. It's easy to lay it out inside the head, but what the head's actually doing is running a script that does a search request every time a vehicle record is loaded. So yeah... no simple way to do it.

Thanks for helping me figure that out comment!

Edited October 10, 2015 by Buckie

[Wim Decorte]

Out of curiosity, when would script-assisted data entry would be beneficiary? I usually shy away from it and create a boatload of relationships instead. Can you describe the situations when it would be better to use scripting instead of relationships?

I really don't follow your reasoning.

Relationships are for viewing and following data.  The data is not created by the virtue of having relationships.  Either the user manually creates data or you have script create it as part of a workflow.

In your example: by entering a new bulletin and its VIN ranges, you can make vehicle data show up.  Provided that the vehicle data already exists.  Now if a car comes in and is worked on as part of such a recall, someone has to enter the data of the time, who worked on it, etc...