summarising data in portal?

[Not so Divine one]

hey all, hope somebody can help. What I am trying to do is very simple I am sure, but I think I must have brain fog as I've stumped myself!  

To keep it easy, I have three key tables: 1) Days (where each record is a unique date) where lots of other information is held in other fields. 2) Foods (with a list of possible foods that can/have been consumed) together with extra information such as calories etc..  and 3) Meal Names ('Breakfast, Lunch etc). 

In the middle of the 3, I essentially have a join table 'Food Line Items' to allow many-to-many relationships between them all. ie. each meal can have multiple foods, each day can have multiple meals etc. 

 

This means that for each day, each food item consumed is added on a portal on the 'Day' form which I can sort by time etc... The user can also add which Meal name it relates to from a drop down menu linking to the Meal Name table. This all works fine.

What I need help on is this:

From each of the 3 key tables, there is a direct relationship with the Food Line Items table. This also works fine so for each day/meal name or Food, I can easily see all the relevant related food items. 

But I'd also like a summarised version. I could easily do it in a report using summary, sub summaries etc.. But want it live in a second portal. So on the Day form, the existing detail portal would show every food item, but the new summary one would simple show 'Breakfast - x, Lunch - y, Dinner - z etc..  

 

I have tried so many ways. I have tried using so many combinations if If calculations, self joins, GetSummary fields but no luck. I can get each meal in a separate field but that will look a bit rubbish in the portal with up to 7 different meal types. Ideally I want it in just one field which summarises for each meal on each day, the total number of calories of related line items. 

 

Hope this is clear. Hope somebody can help 🙂

 

Davinity x

[comment]

1 hour ago, Not so Divine one said:

1) Days (where each record is a unique date) where lots of other information is held in other fields.

What kind of "other information" is held in this Days table, other than the date?

 

[Not so Divine one]

13 minutes ago, comment said:

What kind of "other information" is held in this Days table, other than the date?

 

generally just day-specific info, typically stuff such as any particular flags (whether person describes it a a binge day, bulimic day, fasting day), any general comments, thoughts, feelings. Also likely to store weight results too, though as this is typically a daily result, there's no reason weight couldn't be held in a separate table on a 1:1 relationship too

[comment]

I see.

Well, one way to achieve your goal would be to add a global date field to the Meals table and populate it with the currently viewed date (you can use an OnRecordLoad script trigger for this). Then define a new relationship between the Meals table and the join table matching on both date and meal. This will allow you to summarize the calories for each meal on the currently viewed date, using a calculation field in the Meals table. Show this field in a portal to Meals, based on a relationship using the x relational operator.

Another way can be seen here: 
https://fmforums.com/topic/71906-getting-more-out-of-filtered-portals-3unique-values

 

 

[Not so Divine one]

18 minutes ago, comment said:

I see.

Well, one way to achieve your goal would be to add a global date field to the Meals table and populate it with the currently viewed date (you can use an OnRecordLoad script trigger for this). Then define a new relationship between the Meals table and the join table matching on both date and meal. This will allow you to summarize the calories for each meal on the currently viewed date, using a calculation field in the Meals table. Show this field in a portal to Meals, based on a relationship using the x relational operator.

Another way can be seen here: 
https://fmforums.com/topic/71906-getting-more-out-of-filtered-portals-3unique-values

 

 

thank you, I'll take a peek and see how I get on 🙂

[comment]

Here is yet another way you could look at it:

 

FilteredSummary.fmp12

Edited April 10, 2023 by comment

[Not so Divine one]

On 4/10/2023 at 10:00 PM, comment said:

Here is yet another way you could look at it:

 

FilteredSummary.fmp12 176 kB · 6 downloads

amazing too, thanks so much! xx