Showing data from three tables on a single layout.

[Simon B]

Hello all and thanks for your time reading this. 😀

I have 3 tables :

Call

order

items

Relationships : 1 call has many orders, 1 order has many items.

I am trying to design a layout where I can display the call information at the top of the layout, then the multiple orders information in a tab control object (one tab per order) and then the items information related to the order displayed in the selected tab.

image

I have created a portal in each control tab showing the order (I only display 1 row with the initial row set to 1 in the first tab, 2 first the second tab and so on), that seems to work, but now I want to display in a second portal the items related to the order that are displayed in the active tab. That's where it doesn't work anymore.

 

Any clue to make this layout work or designing the layout differently would be very much appreciated.

 

Have a nice day,

Simon

[comment]

4 hours ago, Simon B said:

multiple orders information in a tab control object (one tab per order)

That's not a good idea. A call can have any number of orders, whereas a tab object has a predefined number of panels. There are several ways to create a horizontal display of records (such as your idea of splitting a portal to individual rows), but it always comes to this: Filemaker is not flexible in the horizontal direction; only a portal or a list/table layout allow you to display an unlimited number of records - and both will do it vertically.

In any case, all you need to do is place the selected order's ID in a global field, and define an auxiliary relationship to the Orders table (or directly to the Items table) using the global field as the match field. There is no need for a tab control at all; even in your current attempt you could use the individual portal rows as fake tab headings and a single portal below that will change its content as you select another "heading". But in order to keep it flexible, you should just use a regular portal to show all related orders (think of it as a tab control with the labels on its side instead of at the top).

I should mention that it is possible to build a horizontal display that would simulate scrolling left and right - but it's not a trivial undertaking. You can see one example here:
https://fmforums.com/topic/71939-getting-more-out-of-filtered-portals-4horizontal-portal/

 

 

 

Edited December 1, 2023 by comment

[Simon B]

Thanks comment for your nice response.

15 hours ago, comment said:

That's not a good idea. A call can have any number of orders, whereas a tab object has a predefined number of panels.

I hear you and I would really like to have a solid and flexible way of doing this.

OK, let's try to do it the right way:

I created a portal on the left side of the screen referencing the orders for the call in a vertical list. Now, when I click on an order in that list I want the detail of that order to be displayed in the second portal.

To achieve that, I store the UUID of the selected order from the left portal in a global variable $$activeOrder (it works fine).

Then on the second portal (the one displaying only one row), I filter the portal according to the UUID stored in the global variable : If ( ORDER::id = $$activeOrder ; 1 ; 0 ), but it doesn't work...

I believe the answer is in this quote, but I couldn't make sense of it :

18 hours ago, comment said:

In any case, all you need to do is place the selected order's ID in a global field, and define an auxiliary relationship to the Orders table (or directly to the Items table) using the global field as the match field.

 

Simon

[comment]

If you want to do this using only a variable and portal filtering, without adding any fields and relationships, you can. However, this is only practical for a relatively small amount of records. If a call can accumulate more than a few hundred related items, you will want to switch to a dedicated relationship.

57 minutes ago, Simon B said:

I filter the portal according to the UUID stored in the global variable : If ( ORDER::id = $$activeOrder ; 1 ; 0 ), but it doesn't work...

See if refreshing the portal object helps.

BTW, your expression:

 If ( ORDER::id = $$activeOrder ; 1 ; 0 )

is a long and roundabout way to say:

ORDER::id = $$activeOrder

This is already either true or false. There is no need to convert it to either 1 or 0, then evaluate the result as Boolean again.

 

[Simon B]

Yes, it does work after I added a "refresh portal" at the end of the script that stores the UUID of the selected order in the $$activeOrder.

I am still fighting with the the parenting of the "items" to the right "order" on creation and when to refresh the portals.

1 hour ago, comment said:

If you want to do this using only a variable and portal filtering, without adding any fields and relationships, you can. However, this is only practical for a relatively small amount of records. If a call can accumulate more than a few hundred related items, you will want to switch to a dedicated relationship.

My "solution" might not be as strong as the one you suggest but I am a bit clueless of the kind of relationship it would require. Could you put me on the right track ?

Many thanks,

Simon

[comment]

10 minutes ago, Simon B said:

I am a bit clueless of the kind of relationship it would require.

Something like:

With this in place, you can place fields from Child 2 and a portal to GrandChild 2 on the layout of the Parent table, and they will show data from the selected Child record (whose ChildID is held in the global gSelectedChildID field) and its children in the GrandChild table.

A more sophisticated version would use an unstored calculation field instead of the global, which would point to a global variable. This would allow you to maintain a unique variable for each parent record, so that each parent retains its own selection for the duration of the session.

 

[Simon B]

Moving forward but still far unfortunately.

I did the table layout and relations like you said, I even went for the sophisticated version using the unstored calculation pointing to a global variable.

But, the unstored calculation doesn't update according to the global variable.

 

[comment]

1 hour ago, Simon B said:

the unstored calculation doesn't update

It will update "as needed" - e.g. when you refresh the window or a layout object that relies on it (if you choose to refresh the window instead of multiple objects, select the "Flush cached join results" option).

 

1 hour ago, Simon B said:

I even went for the sophisticated version using the unstored calculation

Yes, but the sophistication lies in having an independent variable for each parent record. I usually do this by defining the variable as:

but I am using serial numbers for ParentID. I don't think this will work reliably with UUIDs.

 

 

[Simon B]

Yes, I might have been a bit presumptuous with my level of sophistication. 😆

I managed to make it work with the global field. I made a script trigger on the Order portal that copy the UUID of the selected Order to the gSlectedOrderId of the CALL table :

Thanks comment for the help.