one to many, many, many, many problem

[pfrings]

I posted this problem earlier but in an incomprehensible way. Hope this is clearer...

A record in Table 1 may have several or no related records in Table 2.

The same record in Table 1 may have several or no related records in Table 3.

The same record in Table 1 may have several or no related records in Table 4

(and so on for tables 5 to 8).

What I want is to have a few fields from each of Tables 2 to 8 showing through a single portal on Table 1. If, for the sake of argument there were 2 related records in each of Tables 2 to 8, my portal on a Table 1 layout would show 14 rows. Obviously I need some kind of linking table.

Is this possible, and if so how?

[Ender]

A portal can only show records from one other table. What is it that's in these tables that their contents should be viewed together?? If they're the same type of thing, they should be combined into one table. Then this is very easy to deal with.

[pfrings]

Tables 2 to 8 contain specifications for different components of a direct marketing print project. Table 1 draws them all together as a single quote for the client. Table 2 is envelopes, which need different fields in order to be specified to a supplier than continuous stationery print (Table 3) and so on through to Table 8 which is for postage costs. Table 4 is sheet or web fed print items. There would typically be 2 envelopes items, (but sometimes only 1), generally only 1 continuous stationery item (but frequently none on a low volume project) between 1 and 4 print items, and so on.

They really aren't the same things. However the fields which I want to draw into the Table 1 quote ARE the same (Title of element, quantity of element, cost of element etc).

I've been trying to get a 'Quote Lines' Table to work. This would ideally hold these common fields, related to Tables 2 to 8. Then, as you say, it would be easy to show them on Table 1.

But try as I might, I can't get it to work with each field only existing in a single table. Whatever I try, I end up with a schema that means a script has to create a copy of the common fields in an intermediary table (eg Quote Lines). Apart from the principle of not holding data in two places, it is also proving very difficult (for me at least) to write a script which copes with the complexity of checking whether a Quote Line record already exists of not before creating a new record, if it does exist just updating it, and also making sure users to produce quotes which don't reflect changes that have been made to the common fields... I just feel this scripted creation of a duplicate record is probably barking up the wrong tree entirely.

[Genx]

.. why would you do this?... i mean where is the logic?... none of your information in tables 2-8 is related to any other information in tables 2-8... so what possible reason could you have for wanting to put them in one single portal?... if you really want, just put in 8 seperate portals...

Hehe, if you have a logical reason, im actually curious, but just think about your structure first...

Genx

[Genx]

Hold on, are the values in increasing table numbers dependant on previous tables... i.e. is table 3 dependent on table 2 which is dependant on table 1?... because if not, i really am confused, why dont u just put all the information from tables 2-8 into one table and provide numbers for quantity or something...

Edited January 8, 2006 by Guest

[pfrings]

No, tables 2 to 8 are all independent of each other.

I can't/don't want to put tables 2 to 8 in one table, because each has a lot of fields unique to that table. Also, each record in each of tables 2 to 8 can potentially generate a purchase order... and a purchase order is linked to an individual specification (i.e. a record in any one of tables 2 to 8). Putting all the tables into one would result in over 300 fields in one table. Using one record for every single element would leave 90% of the fields empty and cause problems with the user interface.

At the moment I do have them coming through onto Table 1 via eight portals, but it causes problems with the user interface again... because when a user is reviewing a summary of all the quote lines, screen space means you have to limit each portal to a certain number of rows. If on a particular project there are no related records for tables 2 to 4, for example, that screen space is wasted. If I've set 4 portal rows for, say table 5, but there are 5 elements, then if the user doesn't notice that the scroll bar on the side of the portal has changed colour, they won't see the fifth element....

Edited January 8, 2006 by Guest

[Genx]

Well unfortunatley there is no way to populate a single portal with related records from multiple tables...so your only choice is to change your gui or the way an order or whatever is processed... maybe tab your layout?...

[Raybaudi]

The new Tab panell can't help ?

I mean for screen space problem !

You could make a panell with 7 TABs, each holding a portal.

[pfrings]

Thanks for the responses. Yes I did have each of tables 2 to 8 showing through a portal on a different tab panel. But this caused difficulties in navigating back to a particular panel after the user has gone, for example, to a search screen to search Table 4. I couldn't find a way of reliably getting a user back to the tab panel they started at. So I created 9 layouts within Table 1 which each look identical to the tab panel approach, are in fact separate layouts.

So my users can quickly move between a portal for Table 2, a portal for Table 3 and so on, at the click of a navigation button. But what I can't do is show users a few common fields from each table in a clear layout. At the moment I do this through a layout which contains 8 portals, each set to display a certain number of portal rows. But this has the disadvantages described in an earlier post.

Obviously when a layout containing all 8 portals is previewed, all the space taken up by empty portal rows, or portals with no related records, can slide up... but then its no longer editable, which I want it to be before the user goes to a layout for a print out.

Maybe what I'm after just can't be done???

[IdealData]

Table1 needs some more fields [calculations] to represent the pertinent fields from Tables2-8. Then make a self-join relationship for Table1::Table1 and use the new fields in the portal using the self-join relationship.

This will minimise the data redundancy you expected and give the illusion of consolidating your data from multiple tables. Effectively this is an "over view" portal.

If you want them to edit the data in this portal then you'll need to script your way around to the correct related table/record as the fields in Table1 are calcs, so not editable, but that would be correct any way.

[pfrings]

Thanks for your advice. What I'm not sure I understand is how a calculation field in Table 1 could pull in data from several other tables... And if I have a different set of calculation fields for each of Tables 2 to 8, I'm not sure I've made any progress??

[Ender]

I've been trying to get a 'Quote Lines' Table to work. This would ideally hold these common fields, related to Tables 2 to 8. Then, as you say, it would be easy to show them on Table 1.

I think this is the best solution I've seen here.

I would not attempt IdealData's suggestion of using calcs to pull the data from the other tables. Instead, I would simply store those pieces of data that are part of the Quote, in the Quote Lines table. I don't see why they would need to be stored in the each separate component table.

Admittedly, I don't have a full understanding of your structure ("Table 1", "Table 2", etc. are not very helpful,) but I think this would be a case where the structure should be ironed out on paper in the form of a data model. It's important so you know where the best place is for each field. It just seems like some of your fields are in the wrong tables.

[pfrings]

I think the penny has finally dropped... thanks for your tip, Ender, or always going back to a blank piece of paper when struggling to work out the right structure.

My hang up was that I only wanted a user to have to 'create' a quote in a single action. But I can see that typing in a Quote Lines portal on a Quotes (Table 1) layout will create a Quote Line record. All I have to do then is get the user to enter some ID/number from the new Quote Lines record into either a layout/portal from another table, to link that particular specification element/record to the Quote Line record.

The user now has to do two explicit entry actions for every Specification element that builds up the final quote, but I think that is probably a price worth paying for the benefits.

Now to see whether I can get this to work in practice as well as on the sheet of paper!