Need help with ESQL Syntax

[Rich S]

If there's a native way to do this in FileMaker Pro (v20.x) I'm all for it, otherwise I need a little help with an ESQL calc.

Please refer to the attached.

Total is a calculated field that looks refers to Pages or # Originals and QTY or # Copies. (It has a bunch of different calculations based on Product Code, so that's why the one for W11 looks funky.)

Within the child table, I'd like to automatically copy/calculate the value from the Total field of records that have a Product Code of W11, into those records that have a Product Code of L8511; so the calculated value in Total for W11 is also in Total field in records where the Product Code is L8511. 

I thought of using an ESQL calculation in a discreet calculation field that looks at the foreign key from the parent, "finds" the record in its found set with a Product Code of W11, then outputs the value from the Total field, but I can't nail the syntax for the calc. 

Maybe another T.O., with a foreign key join, would work but how would I "see" the records with W11 in the Product Code field?

As always, TIA for your help!

 

 

ExecuteSQL ( " SELECT ( Total__lcn )

FROM ORDER_FORM_CHILD

WHERE \"_kflt_Order_Form_ParentID\" = ? AND

ORDER_FORM_CHILD.ProductCode__lxt = 'W11'

" ; "" ; "" ; ORDER_FORM_CHILD::Total__lcn )

[comment]

2 hours ago, Rich S said:

I'd like to automatically copy/calculate the value from the Total field of records that have a Product Code of W11, into those records that have a Product Code of L8511;

What if there is more than one child record whose Product Code is W11? Which record should you copy from then?

Overall, this seems like a very strange requirement. As a rule, you would expect child records to be independent of each other, with exceptions handled by a self-join matching on a common value - such as aggregating line items of the same product or type of product. You don't say what is the purpose behind this, and I suspect it is a workaround for a deeper structural problem. 

 

2 hours ago, Rich S said:

Maybe another T.O., with a foreign key join, would work but how would I "see" the records with W11 in the Product Code field?

Well, you could define a calculation field that outputs "W11" if the local Product Code is "L8511". Then use this to define a self-join (along with the parent ID field, IIUC). Whether that is a good solution I don't know. But it would probably be better than using ESQL in a calculation field. 

As for your syntax, I believe the parameter needs to be _kflt_Order_Form_ParentID, not ORDER_FORM_CHILD::Total__lcn  (untested). But again, that would be my last choice, not first.

 

 

[Søren Dyhr]

On 2/20/2024 at 11:58 PM, comment said:

What if there is more than one child record whose Product Code is W11? Which record should you copy from then?

Could be prevented, by dwindling the value list behind Product code ... with one of the tricks shown here: https://filemakerhacks.com/2012/07/25/magic-value-lists/

But I think as well, there must be a structural problem here ... I'm missing the SQL word JOIN, in the statement. A very balanced examination of the topic could be studied here again: https://filemakerhacks.com/2012/10/02/outer-joins-in-filemaker-12/ ... and onwards in the thread.

--sd

Edited February 22, 2024 by Søren Dyhr

[comment]

1 minute ago, Søren Dyhr said:

Could be prevented, by dwindling the value list

I don't know that it needs to be prevented... But if it does, it should be enforced by validation at the data level, not (only) by layout-level measures.

Ultimately, though, if only one child value is allowed then this is likely to be an attribute of the parent. Which is why I suspect this is a structural problem. 

 

8 minutes ago, Søren Dyhr said:

I'm missing the SQL word JOIN

Why? It's a simple query: find records with the same ParentID as the current record and with Product Code "W11". No related values are involved.

 

[Søren Dyhr]

17 minutes ago, comment said:

Why? It's a simple query: find records with the same ParentID as the current record and with Product Code "W11". No related values are involved.

I thought it was a total of all open orders regardless of clients ... is the term "at hand" ???? ... there the delivered were added with a negative figure.

--sd

[comment]

I don't know. If I had to guess, I would say this is a "kit/component" problem. Looking at the screenshot, it seems they ordered 30 white pieces of paper (W11) that need to be laminated (L8511). They entered the quantity in the W11 item and they want it to be automatically copied to the L8511 item. 

 

[Rich S]

Correct, Comment. The children table is joined to yet another table so using the third table's foreign key would prevent same-name codes from being included.

I'm embarrassed to say the database was put together with a poorly thought-out schema--it's one of my earlier efforts from years ago that's sorely in need of an overhaul.  

Maybe, instead of using a direct calculation a script--fired by an OnObjectEnter trigger attached to the Pages or Quantity fields--would be a better way to go; it could parse the child records, loop through the found set, set a variable when it "finds" the record with W11, then pops the variable's quantity into the L8511 record. Of course, it would only succeed if the W11 record is before the L8511 record. Ugh. : P 

 

 

[comment]

I don't know of a really good general solution to this type of problems. I believe it might be useful to consider the user interface separately from the underlying data structure.

In terms of UI, "laminate all" is an option of W11 and it should be selected when adding such item. This would then run a script that would create the data in the desired data structure - e.g. an additional line item as in your example.

 

[Søren Dyhr]

21 hours ago, Rich S said:

Maybe, instead of using a direct calculation a script--fired by an OnObjectEnter trigger attached to the Pages or Quantity fields--would be a better way to go

Hardly, and definitely not the classic filemaker'ish way to do it, is the task here to find and count all records which have option W11 chosen, regardless parenting?

--sd