Relationship to filter records based on grandchild Table::Field

[RSGC]

Hi - I feel this must be addressed here somewhere, but I can not find it. 

3 Tables:

Invoices with fields: PrimaryKey; isLabor (Global); and others

LineItems with fields: PrimaryKey; InvoiceID (ForeignKey); LineItemTypeID (ForeignKey), Quantity; Quantity(Summary-Total of Quantity); isLabor (Global); and others

LineItemType with fields: PrimaryKey; isLabor; Quantity; and others

The invoice layout has invoice fields, and a portal to Line Items. That is all working fine. 

I woud like to put a summary of the Quantity field on the invoice layout outside the portal, but have that quantity ONLY add up rows in the portal where "isLabor" in the LineItemsType is "Yes." I figured I would make a second table instance of LineItems with a relationship using the primary and foreign key fields, and then then a second criteria using the isLaborGlobal and isLabor field in the LineItemType. But can not use that grandchild table in a relationship.

So then I added a second instance of LineItemType and made a relationship from the second instance of LineItems to the second instance of LineItemType and used two criteria: one linking primary to foreign keys and one linking isLaborGlobal to isLabor. I was successful (meaning I was able to make this setup, not sure it is the right thing to do).

Then I put the Quantitysummary field from the second instance of LineItems on the layout. It shows the sum of all Quantity row regardless of the value in lineitem type::isGlobal. I then added a portal to the records via the second instance of the LineItems table. And sure enough it shows all rows regardless of the value in isLabor.

I am open to other suggestions of how to accomplish this. 

Bob

 

 

[comment]

There are a couple of things in your setup that I don't understand:

• What is the purpose of the isLabor (Global) field in LineItems?
• What is the purpose of the LineItemType table? And why does it need to have a Quantity field?

I would have expected the types to be a value list, not a table. And although you could define a dedicated relationship by type between Invoices and LineItems, I believe a filtered portal would be quite sufficient.

 

Edited April 14 by comment

[Søren Dyhr]

16 hours ago, RSGC said:

So then I added a second instance of LineItemType and made a relationship from the second instance of LineItems to the second instance of LineItemType and used two criteria: one linking primary to foreign keys and one linking isLaborGlobal to isLabor. I was successful (meaning I was able to make this setup, not sure it is the right thing to do).

Well I kind of agree with the filtering of the portal, but I would make copies of the original and then apply filters to each of the new ones, and put the summary field in it. Inspired by: https://filemakerhacks.com/2012/09/13/aggregates-in-filtered-portals/

Then is there the matter with the second instance of lineitems ...apparently for some selfjoin summing with both the predicate key as well as the type chosen as key, this was probably the way I would have solved it, before filtered portals appeared, but using data several relational jumps is quite burdening when rendering a very complex layout. 

On the other hand is filtered portals not particular lively, and requires freshing from event-triggers that's why in my included interpretation of the matters at hand, have scripted the freshing. 

Then is there a matter, of some concern, prices as well as Labour ... ought to be lookups, the first because prices are likely to change from invoice to invoice, and the latter because it stores the change of Labour or not from line to line in order to change the fields scripted behaviour.

--sd

Invoice.fmp12

Edited April 14 by Søren Dyhr

[RSGC]

Sorry, apparently I was staring at the screen too long when I wrote that. Here is the current table/field setup:

Invoices with fields: PrimaryKey; and others

LineItems with fields: PrimaryKey; InvoiceID (ForeignKey); LineItemTypeID (ForeignKey), Quantity; Quantity(Summary-Total of Quantity); isLabor (Global); and others

LineItemType with fields: PrimaryKey; TypeName; isLabor; COAnumber; TaxID; and others

In addition I seem to have caused other confusion. My goal is NOT to end up with a portal that is filtered. I added the portal filter just to try and sort out if any filtering was going on. That portal will be deleted. I want a portal to remain that shows all the LineItem records. That is currently working. 

LineItemType has a number of other fields, and will be some what dynamic when the solution is in use. I am not sure how to use a value list when there are currently 8 other fields, and possibly more in the future, associated with each LineItemType record in addition to the "type name."

My goal in having a filtered relationship is to have a summary field on the invoice layout that is a total of all LineItem::Quantity where the the LineItemType::isLabor is set to "Yes." There will be a number of records where LineItemType::isLabor is "Yes" and a number of records where LineItemTypes::isLabor is set to "No."

There is no SelfJoin, I don't think. I made the send LineItems instance, thinking I would filter it to only records where LineItemType:isLabor" is yes, and then use the summary of Quanttity to display on the invoice layout from this instance. 

I don't believe the price (I don't have such a field) plays into this situation. I do have Cost and TotalCost field in LineItems, but they are not looked up or related. Quantiy and Cost will be typed in by the user as each LineItem is created. We do not maintain an inventory of items for sale, so there is nothing to relate to or lookup. 

Sorry for my confusion creation. As mentioned originally, my "plan" may be the wrong way to approach this entirely, and I am happy to learn another means to end up with a LineItems::Quantity sum displayed on the invoice layout that only totals LineItems::Quantity where LineItemsType::isLabor is "Yes." 

bob

[comment]

43 minutes ago, RSGC said:

There will be a number of records where LineItemType::isLabor is "Yes"

Ok, that makes it a lot clearer. Though I still don't see why there is a global isLabor field in the LineItems table.

I think the first question you need to ask yourself is what should happen if the isLabor field in the LineItemType table is modified; how should this affect past invoices?

If (as I would expect) the answer is that past invoices should remain unchanged, then there needs to be an isLabor field (not global) in the LineItems table, and it needs to be populated by a lookup from the LineItemType table.

 

43 minutes ago, RSGC said:

means to end up with a LineItems::Quantity sum displayed on the invoice layout that only totals LineItems::Quantity where LineItemsType::isLabor is "Yes." 

I still think that a filtered portal is the optimal solution. You seem to think of a portal that would list all line items in the "labor" category - but what I have in mind is a one-row portal containing only the summary field.

---
Side note: the isLabor field, like all Boolean fields, should be a Number field containing the value of 1 when true, and the value of 0 or empty when false. You can format the field to display as "Yes" or "No" - but those should not be the actual values stored in the field.

 

Edited April 15 by comment

[RSGC]

I was thinking that the two criteria in the relationship linking LineItems2 to LineItemsType2 would be:

   LineItems2:LineItemsTypeID = LineItemsType2::PrimaryKey

   LineItems2:isLabor(Global) = LineItemsType2::isLabor

And LineItems2:isLabor(Global) would always be set to "Yes."

I had not considered anyone changing the LineItemType::isLabor value. Hard to imagine why that would be changed, but users being users, anything is possible. A lookup seems like a good solution, and I suspect ends up simplifying the solution to my problem. I have never done a one row portal, so it is not a idea I would come up on my own. 

Many thanks for the guidance. 

Bob 

[comment]

If you wanted to solve this by having a dedicated relationship, I would still start by asking the same question:

16 hours ago, comment said:

what should happen if the isLabor field in the LineItemType table is modified; how should this affect past invoices?

Assuming that the answer is again that past invoices should remain unchanged - i.e. that there is a isLabor field in the LineItems table that looks up its value from LineItemsType - then the relationship would be defined as:

Invoices::PrimaryKey = LineItems2::InvoiceID
AND
Invoices::One = LineItems2::IsLabor

where the One field is either a global or an unstored calculation field containing the value of 1.

But typically an invoice contains only a handful of items, so the performance tax of a filtered portal is negligible and there is no reason to add more complexity to the relationship graph. This is assuming that the subtotal of "labor" items is used for display only. If you intend to use it for further calculations then the answer may be different.

 

Edited April 16 by comment

[RSGC]

Yes, display only. having the isLabor value lookedup into the LineItems table seems like it will make it simpler all around. Many thanks. 

Bob

[comment]

6 minutes ago, RSGC said:

having the isLabor value lookedup into the LineItems table seems like it will make it simpler all around

Actually, the portal can be filtered just as well by looking at the value in the LineItemType table. But having the lookup is good practice nevertheless, because things do change.

 

[RSGC]

This is working as you described. Thank you for the assistance.

The only gotcha I see is the summary fields (now located in a one row portal as you suggested) updating (or not) on screen. I have tried a onObjectModify script trigger to run RefreshWindow. I have tried with "Flush Cached Join results" selected and not slected.  With it not selected the summary field continues to show the original value after a field change that should update the value. With it selected the summary field goes blank after the same change. 

A subsequent click outside the portal causes the field to update with the new correct value in either case. So it seems I am close. Hoping to find way to force the summary field to update and show when a related value changes. Having it be blank is better than the wrong value, but that is a litle wonky. 

Bob

[comment]

Typically a summary field will update only after records have been committed (as would normally happen when you click outside of any field).

 

[Søren Dyhr]

1 hour ago, RSGC said:

So it seems I am close. Hoping to find way to force the summary field to update and show when a related value changes.

Under each modifiable field, could the developer establish the summary via "on modification" script trigger use the GetSummary( function to load a simple number field, instead of the Beverly Voth one-line portal. It's a spreadsheet'ish behaviour we're after here, which doesn't quite rhyme with the gist of relational databases ... I'm afraid. It could be said that "found sets" only exists in spreadsheets as such, never in SQL and only ....to a certain degree in filemaker. A developers vanity plays in here!

Something must be instated to prevent long chains of unstored calc's to update/adjust themself allthough it can be accomplished with some yawning ... and "grandparenting" relations fall into that category - am I afraid!

Clicking outside a field, is however often done scripted with: Go to Field[select/perform]

Thought provoking issues however exists in this: https://filemakerhacks.com/2013/07/28/forgetsummary/ 

--sd

[Søren Dyhr]

20 hours ago, RSGC said:

A subsequent click outside the portal causes the field to update with the new correct value in either case. So it seems I am close. Hoping to find way to force the summary field to update and show when a related value changes

Have changed the template I made som days ago ever so slightly:

InvoiceCached.fmp12

It's Michail Edoshins technique Fast Summaries in action here! But because only a binary value is used here would there not be need for counting summaries in order to jump from group to group, first and last record would here suffice!

--sd

Edited April 20 by Søren Dyhr

[RSGC]

Thanks to you both. I am not sure I totally have my head wrapped around all of this, but I have managed to make what I needed (wanted) to show up where and when I want. 

Bob

[Søren Dyhr]

13 hours ago, RSGC said:

Thanks to you both. I am not sure I totally have my head wrapped around all of this, but I have managed to make what I needed (wanted) to show up where and when I want. 

The reason to choose the cached version of the matter here, is that you could locate orders where say more than 10000 labour hours is used, because you're avoiding to make searches  in  unstored fields which, both sum( and genuine summaries are. 

Try to make a million dummy records, where both the sum( and summaries are shown as a result on the layout ... and learn that Filemaker can get a bad reputation, for not giving instant answers ... even thoug it's mainly down to the developers choice of tools to take the blame.

--sd 

Edited April 21 by Søren Dyhr