Subtotals

No proposals yet? Let try something… From the formulas listed, it seems that a relation  exists between "Activity" and "Ingredients"; I supposed that one also exists between "Tickets" and "Activity", which makes sense since each record in "Activity" belongs to one Ticket.

In the proposed solution, the "Action cost" field is not needed.

Sometimes, after building a solution to a problem, further study reveals that the first try can by streamlined. In this case, a much simpler solution is indeed possible…

I'll try to explain how it works.
As a first step, we put the identity of the chosen record from the "Tickets" table in the "focus" variable, since we will need it at several places:

let focus := record(Tickets,number('PICK WO'));

We need to produce one line of output for each unique ingredient in the current ticket. So we follow the chain from the record of interest:

let matls := unique(focus.'MTL ACTIVITY'.'Material Select');

through the 1:N reference to the activities:

let matls := unique(focus.'MTL ACTIVITY'.'Material Select');

and then to the ingredient referenced, N:1, by each activity:

let matls := unique(focus.'MTL ACTIVITY'.'Material Select');

This gives an array of records identifiers from the "Ingredients" table, whose number is equal to the number of activities, with possible duplicate ingredients. Applying the "unique()" function keeps only one record for each distinct ingredient. The "unique()" function works perfectly on record identifiers. So the variable "matls" now contains an array of record identifiers, one for each distinct ingredient.

Time to build the result. We will build a new array, containing the output strings. A "for" loop is a control structure, but in Ninox, it also returns a value. This value is always an array, consisting of one element for each iteration. The value of each element is the result of the last computation performed into the loop.
So we iterate on the elements of the "matls" vector, sorted by the name of the ingredient contained in a "Text" field:

let label := for inc in matls order by Ingredient do

Here comes the tricky part. For each ingredient, we need to find all the activities, in the ticket of interest, making use of it. So we start with the ingredient designated by the loop variable:

let actuals := inc.Activity['Activity to WO' = focus];

and follow the reference, 1:N, to all the activities using that ingredient:

let actuals := inc.Activity['Activity to WO' = focus];

Mmmm... this is a bit too much, since we now have an array containing the identities of all the activity records using that ingredient in ALL tickets. We filter that array by requesting that the ticket designated, N:1, in the activities to be kept, is the ticket of interest. Comparisons work fine with record identifiers, too:

let actuals := inc.Activity['Activity to WO' = focus];

So the "actuals" variable contains an array of record identifiers with only the activities we need. We compute the sum of the costs of those activities, and round it:

let xcost := round(sum(actuals.'M Action cost'), 2);

and build the output line:

lpad(format(xcost, ".00"), 10, fsp) + " — " + inc.Ingredient

The final result is computed by joining the elements of the array, and adding the other useful informations. To add the ticket number, we must prefix the name of the field by "focus", to catch the number from the correct ticket.

The "urlDecode()" function is not used to build actual url's, but just to create unicode characters from their code.

let xlf := urlDecode("%0A");

The "xlf" variable contains the "linefeed" character, used to separate the lines in the output. It is an alternative to putting an actual linefeed inside double quotes, which, when there are many of them, makes the formula extend vertically beyond control. One may like this style, or not…

let fsp := urlDecode("%E2%80%87");

The "fsp" variable contains the "figure space" character. It is used in the "lpad" function to neatly align the numbers. One may see that as icing on the cake.

The "Total Cost" is computed by summing the rounded values in the different lines:

let xtotalcost := 0;

before the loop, and:

xtotalcost := xtotalcost + xcost;

inside the loop. No "let" here, as this would create a new variable instead of adding to the one defined outside of the loop.

A nearly identical formula may be included in the "Tickets" table, to show the same information in each ticket. The differences are that the "focus" prefix is not needed, since we access the fields of the current record, and that the "focus" variable, still needed to filter the activities, is initialized by:

let focus := this;

If you consider that the question has been answered, don't forget to set the "ANSWERED" status for the thread.
 

let xlf := urlDecode("%0A");
let fsp := urlDecode("%E2%80%87");
let focus := record(Tickets,number('PICK WO'));
let matls := unique(focus.'MTL ACTIVITY'.'Material Select');
let xtotalcost := 0;
let label := for inc in matls order by Ingredient do
        let actuals := inc.Activity['Activity to WO' = focus];
        let xcost := round(sum(actuals.'M Action cost'), 2);
        xtotalcost := xtotalcost + xcost;
        lpad(format(xcost, ".00"), 10, fsp) + " — " + inc.Ingredient
    end;
"TICKET # " + focus.Ticket + xlf + xlf + join(label, xlf) + xlf + xlf +
lpad(format(xtotalcost, ".00"), 10, fsp) +
" <<< TOTAL INGREDIENT COST"