Jump to content

Many to Many relatonship with a twist


This topic is 6123 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Pic1n2.jpg

pic3r.jpg

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Pic4.jpg

Link to comment
Share on other sites

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 ??

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Pic5.jpg

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6123 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.