line items from multi-table db

[merkaba22]

I love portals -- especially since I have got them to have popup lists that depend from other popups in the portal lines via value lists and lookups from the realtionship.

I was just wondering if FM lets you take it a step farther. Let's say that the first field in the portal line is a popup of category choices (by some sort of calc) that represent different tables such that by choosing one a relationship is defined to the table of choice?

Then if that was possible, would it be possible to define subsequent fields by a calc of some sort that see the relationship defined by the category choice and then would allow these fields to be then populated by lookups accordingly?

Is this possible, a right and resonable use of FM resources, etc.?

Can't use a report -- I need permanant records, etc.

Ex:

Table 1:

field1a

field1b

filed1c

table1_Id =1

Table2:

field2a

field2b

field2c

table2_Id =2

Layout w/ portal

first field includes a popup displaying "1" and "2".

If "1" is selected, values for field1a are presented in a popup and field1b and field1c populate two additional available fields in the portal line.

If "2" is selected, values for field2a are presented in a popup and field2b and field2c populate two available fields in the portal line.

It would be very cool if FM can do this:)

Alternatively, I could create seven portals, one for each table, in my real world example, where only related records were shown and create a script to collectively show them in another layout with format like line items, one one after another, for printing purposes, but I would not have control of the order that they appear conpared to the above solution or some other way of generating this type of record.

Thanks in advance...

[merkaba22]

Jees -- not repsonses after all those views ... am I missing something?

I am attaching a model that does not work .... because I don't know what I am doing .....but shows what I am trying to accomplish. In the opening layout there is a field called table where you could can enter 1 or 2 -- 1 refers to one table and 2 refers to another.

If you select 1 the portal fills up with info from table 1 -- what I would like to construct is the possibility that by entering 2 in the table field that the corresponding data in table 2 would populate the portal.

dunno how to do this -- if possible.

If the second and third fields can have conditional lookups that woulb be masterful:)

Clarity of any type would be greatly appreciated:)

One_to_many.zip

[Søren Dyhr]

Ddn't you recieve my template by e-mail??

--sd

[merkaba22]

Yea, I did thanks but I need to go further;

Been fooling around with this some more .....

I renamed each table in a way that will follow the build I am doing: "Videos", "Info Board" and "CID" with calc fields for the keyfields accordingly. Then I created a popup list in the test file for keyfield with "Videos", "Info Board" and "CID".

When I choose one of "Videos", "Info Board" and "CID" it returns the value for the first record only. Opps for me ....

What I need there is a popup VL that returns the values of all the records for the chosen table so one value can be choosen -- then accordingly, the "nextfield" would have a popup VL based on all the records in the chosen table where that value exists .... Ideally, there then would be a "lookup" for data associated to the record of each table.

For example, in "test": keyfield = "Videos"; "the puck" gives a popup containing: "John Coltrane";" and "The Beatles"; and the "nextfield" returns popup VL for "A Love Supreme" and "Impressions" if I choose "John Coltrane" OR the "nextfield" returns in a popup VL for "A Hard Day's Night" and "Help", if "The Beatles" are chosen. If "A Love Supreme" is chosen, the "lookup" auto populates with "studio".

Similarily, in "test": keyfield = "Info Board"; "the puck" gives a popup containing: "20 minutes" and "10 Minutes" and the "nextfield" returns a VL popup for "rock" and "funk" for "10 minutes" and "jazz" and "funk" for "20 minutes" and so on.... If "jazz" is selected then "lookup" auto populates with "acoustic", for example.

I know how to do this from one table with portals and VL popups very well by now -- can this be done from many tables along the requirements I have?

Maybe portals are needed but I can't see/figure out how to control the relationships to follow the choices made in "test": keyfield ( ie. "Videos", "Info Board" and "CID) .

Here's a new attachment "test3" based on your template:)

test3.zip

[Fenton]

Here's an example that does something like you want. Personally I can't see much use for such a thing, other than as a global choice, for something like navigation. Because otherwise it is basically redundant data, which you don't really even know what it is.

Basically it uses 3 value lists for each set of 3 possibilities, with each of them attached to an instance on the layout of the same field. Only one value list will have any related records, after the choice in the field above it in the hierarchy. The trick is getting the cursor to go into the correct instance. I use a script, which can check which is not empty. It does to the 1st; if it's empty it uses Go To Next Field. So they must be after each other in the Tab Order.

And because this all has to be scripted, you don't want the cursor to just go into the next field after a choice. So I use a "dummy" container global to "catch" the curser, and discourage tabbing.

It has the "lookup" at the end (actually an auto-enter calculation). But it is really not such a great idea. What if there is more than 1 match? It will always just get the 1st. You could alternatively create another similar value list for it.

It's a little awkward. It might be smoother if you triggered the scripts with an "event-aware" plug-in. (I'm in trouble now, 2 plug-in posts in the same day :-)

test3_fej.zip

[merkaba22]

The fields were used to create combinations that a number of which comprise playlists.

In the end I decided to use portals where for each record any number of combinations could be displayed simultaneously.

thanks ... I am always learning:)