Many to Many relatonship with a twist

[Asda]

I am in the process of building a DB and running into a situation where I can use some help.

Db has many tables , but pertaining to this situation, we need consider only 3.

OrderMaster (OM) – Table where individual orders are stored.

StatusMaster (SM)-table where individual statusitems are stored.

StatusJoinTable (SJT) –This is a Join Table between OM and SM.

(One status item can belong to many orders and one order can have many status items).

I currently have portal in OM (from the SJT table) table listing out the various status items and their attributes

(like date rcvd, due date , status etc ).

So ALL the status items show up in this one portal.

Now I’ve been asked if it is possible to show the various status items in different portals.

See pic1 for current situation.

Pic 2 for what I am attempting to do.

In attempting this I realize I have to make many TO’s of the SJT table for each type of status item and then as mnay TO’s of the

SM table.

Each Status item also needs to have its history recorded in a separate history file and a report from the history file

will need to be across one order!

Q1:Is what I am doing the right approach ? see pic 3.

Q2: is this even possible in the realm of DB design.

Q3:what model would you propose I follow…..multiline keys ??

Any insight will be greatly appreciated.

Thanks.

[Søren Dyhr]

Now I’ve been asked if it is possible to show the various status items in different portals

Wouldn't it be obvious to link status as a second criteria, this would require either a global field that changes value according to which layout you wished to show.

With todays tabbed layouts, would it perhaps be better to have an unstored field as the other primarykey reflecting which of the tab's was the frontmost ...but there exists other options:

http://www.kevinfrank.com/download/county-highlight-in-portal.zip

What I here suggests is if it's posible then colour code the status in the portal, but on the other hand is the setting of a global field via a script rock solid, following either the flipping to a new layout or tab. If tabs are the choise here do you need to implement something like this:

http://www.fmforums.com/forum/showtopic.php?tid/181251/post/225426/#225426

--sd

[David Jondreau]

It depends on your display needs, but I think Soren's idea of using status as a match field makes sense. Do you need to display the all the status items at once? If not, you can allow users to select the status item and it will fill in the portal with the correct info.

If you do need to see all the different status items in portals at the same time you will need all those relationships...to the join table. You could cut down on the second relationships by creating an unstored calc field in the join table equal to a concatenated list of the info you're grabbing from those Item tables. You'd only need to do that once.

[Asda]

Thnak you Soren and David for your replies.

So basically I have the following approaches:

1.Stick with the one portal and colro code the diferent status items. This one won't work as it will still be quit confusing for the users to have all the status items in one portal.

2.Use multiple portals on multiple tabs and have each portal display accordingly..so portal on labdips tab will display only labdips. This suggestion is workable....shall explore it further.

This approach will still keep a single Status Join Table file and no need for multiple TO's - right ?

As I will need to generate reports and having a single TO to deal with makes that part easier.

3.I am also tending towards using a global to filter the portal as in pic 4.

Apart from the above suggestions, i will need to use multiple TO etc as advied by David.

Thanks Guys!

I feel areassured I am on the right track.

[Søren Dyhr]

i will need to use multiple TO etc as advied by David

Well it could be done otherwise:

http://fmcollective.com/2007/04/30/just-add-the-table-already/#more-27

--sd

[Asda]

Hi Soren,

yes, I had read that post earlier and was wondering, as was vaughan, as to how an additional table will preclude the need for multiple TO's.

My scenario is the same , where I have many keys but only few of them hodl data at any one time.. but I cannot see how a table will help ??

[comment]

Your options #2 and #3 are practically the same. With tabs, you will be only showing one portal at a time, so you can save on TO's that way. But you need to have a script to switch the tab panel AND change the global value at the same time.

Since the portals in all the tab panels are duplicates of each other, you don't actually need the tabs - you could use buttons to fake the tabs, and just display a single portal underneath. The only reason to use real tabs would be if you wanted to emphasize the switch by changing the colors.

[Søren Dyhr]

as to how an additional table will preclude the need for multiple TO's

Each record in the extra layer, substitutes a TO ... but it requires a trivial mathematical depedency the records in between, but an awfull lot can be established via GetNth( ...it's actually quite inspirering what Michael have found here:

http://www.fmforums.com/forum/showpost.php?post/266405/

Imagine what could be obtained with a slight change in the sortorder!

Perhaps if you descripe the nature of the 7-8 primary keys, I would have an idea of what can be done??

--sd

Edited September 13, 2007 by Guest

[Asda]

Thanks Soren..

Please see a bigger picture of the Relationship Graph.

The yellow TO is the OM Table.

The primary key here is a concactenated key of type text: OM_ION & " "& "labdip"

OM_ION& " " & "Fits"

etc

etc

(yes, the word labdip/fits etc is hardcoded into the field and ION is the internal order Number)

The SLI Table has the same foreign Key in all TO's: _kf_SLI.

[Søren Dyhr]

StatusJoinTable (SJT) –This is a Join Table between OM and SM.

Where is it in the graph, I'm confused here???

The primary key here is a concactenated key of type text: OM_ION & " "& "labdip"

Why do you use concatanations, when you can have several criterias listed in the relations definitions dialog??

Alright I think I understand what you're showing me, and while thinking at it, came I to think of what we call "Ugo's Method" which perhaps is more fun than practical, however are you by it avoiding globals and the concatenated keys of yours, everything is based on unstored values, the keyvalues only exist in the criterias chosen in the many table...

I have exploited this CF:

http://www.briandunning.com/cf/62 ...to make things happen!

If you wish to untangle your graph further, could this be used as well:

http://www.filemakermagazine.com/videos/determining-the-active-tab.html

But then are we there where Comment raised an important point, it might be wiser to graphicly emulate the tabs, since it then is the same relation shown in each tab, only with slightly changed keys, but it then gets scripted. Eventhough I havn't tried it yet, should it be posible completely unscripted!

--sd

Criteriasplit.zip