Many to Many - range relationship

[Anuviel]

I have a basic many to many relationship. Ie.

Two tables. One table contains phone models and the other contains cases.

It is a compatibility checker.

One case can fit many phone models.

One phone can fit many case models.

I have trouble setting up the relationships as my phone fitting factor is a defined range:

Case size: HxWxD in mm

Case: 103x53x20

Phone 102x51x15

The phone will fit this case.

The phone that has dimensions of 100x50x17 will also fit the case.

The phone that has dimensions of 98x50x17 will not fit the case as the case will be to big in H dimension.

The range is H - 4, W - 3 and D - 5

In other words any phone that has dimensions in range of 103x53x20 to 99x50x15 will fit the case that has dimensions of 103x53x20.

The phones that are in the above range, for example, will not fit any cases that have dimensions which are different then 103x53x20.

The goal is to display compatible phone models on the Case layout via portal and vice versa.

Edited November 5, 2008 by Guest

[mr_vodka]

I would start by putting each of those into seperate fields.

Height, Width, Depth.

Then you can use a preferences table to store the allowed variance and reference it for calcs.

In your phone table, create three new calcs.

cHeightAdjust ( Height - Prefs::HeightVar )

cWidthAdjust ( Width - Prefs::WidthVar )

cDepthAdjust ( Depth - Prefs::DepthVar )

Now create a relationship keyed on these three fields to the 3 fields in your PhoneCase table.

Use the ≤ instead of the = as the operator.

[Anuviel]

Heh, thanks. I feel so bad now. That is what I did exactly but I messed up, for some odd reason it did not work. I rechecked after seeing your reply and I mapped the relationships wrong.

Thank you.,

[Anuviel]

Actually, I have it working but I fail to see and understand why a portal on my Phones Layout is failing to show related records from my Case layout. If anyone could take the time to let me know I would appreciate it.

See the portal on the phone layout - that should show related records from cases.

The portal on the Cases layout is working fine and is showing records from Phones.

Thank you.

Size.Check.zip

[LaRetta]

Your Cases calculations are unstored and cannot be used for the child (portal) side of a relationship to Phones.

Edited November 6, 2008 by Guest
Corrected poor sentence

[Anuviel]

Aha - got it. Before I hard coded size calc into the case table so the portal worked as the calc could be stored but since I switched to using global fields the calc became unstored and thus the portal stopped working.

How would I now go to get that portal in Phones table working?

Thank you.

[Anuviel]

I solved the problem. I just abandoned the idea of preference table and have hard coded the calculation and made it stored so both portals now work.

As I am pretty much the only one who will use it (my side business) I see no issue with hard coding it as i can change it on the fly as necessary.

[comment]

Another way would be to define a reciprocal calculation in the other table, and create a separate relationship in the opposite direction.