0

Subtotals

I was playing with the nifty join function to create a "Label".

I am not able to produce the desired results so am posting example to see if I'm missing something or just off the rails.

I am aware of the 'Grouped' function in a View and it does produce the expected result in the view but, as mentioned, I'd rather produce the results in a "Label" format.

Maybe others have advice on how to produce the desired result shown in the last example.

Kind regards.

Given table examples:

Here are sample scripts:

< Use unique to display only1 instance of each ingredient >

Workaround to display subtotals ( use select to create subtotals for each Action record ):

My preference would be to have an arrangement listing the cost subtotal first  followed by the ingredient.

So, I know this is a crazy-talk but, it would be nice if somtheing weird like this worked...

10 replies

null
    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    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.

      • spinner_7580
      • 1 yr ago
      • Reported - view

      Alain Fontaine 

      Alain -

      That looks super clever!  It will take me a bit to parse out the "loop" bits.  I haven't explored that yet.  Yes, there are the relations as you mention.

      My preference was to avoid adding "helper" fields!

      Let see if I can bolt this in.

      Thank you very kindly!!

    • spinner_7580
    • 1 yr ago
    • Reported - view

    Alain Fontaine

    Alain -

    I spent some time squeezing your code into my database - it was hard for me because it feels odd not using Select.   It all works - sort of...

    I'm still trying to get used to your syntax when avoiding the use of Select.  I feel pretty comfortable with Select but have read where it is not the most efficient route.

    Firstly, I don't think I made clear earlier that I am in the "Tickets" table and am displaying the "label" as a sort of Dashboard function by using a dynamic choice field to "pick" tickets.

    In the clip below you can see that my code didn't exactly mirror yours.  I suppose it might be because my  links aren't as simple as your example/interpretation of what I'm up to.  That's why I added the code in line 1 and modified your example in line 3.

    The rest of it was trial and error or rather error and trial.  Maybe I depend too much on the autocomplete function in the editor as a crutch.  In order to mirror your syntax I had to type the links "manually" since the auto-complete wouldn't always find the right data or if I navigated and clicked on the fields in the left sidebar I would have to "edit" part of the path to make it work... real newbe stuff on my part.

    When I say it sort of works I mean that I have to be "sitting on" the record that corresponds to the dynamic choice I pick.  So, if I'm "focused" in the table on the record with WO 123 and then pick 123 in the choice field then the label is built correctly.  If I am not "focused" on the record with WO 123 and pick 123 then the label is not built.

    Maybe it has something to do with using "this" in the code?  Anyway, I couldn't figure a way around that.  Possibly your solution would instead be to "jump" the the record in the "Tickets" table based on the "pick".

    Also, as a rule of thumb, does using your syntax is it really possible to universally avoid the use of Select?  That is, if you're clever enough can you avoid ever using Select?

    At your convenience I look forward to your sage analysis.

    - Kind regards -

    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    In the design of a relational database, one creates tables to model some objects in the real world, and then defines links to represent the relations between those objects. If the database is correctly designed, the normal way to exploit it is to follow the (chains of) links. Database software is supposed to be heavily optimized around those operations. From time to time, it can be necessary to access data from a table for which a suitable (chain of) link(s) cannot be found. For such, normally rare, cases, Ninox has a workaround, the "select" function. "select" can gather data from anywhere in the database, but since it does not use the links, it is much less efficient.

    Back to your problem. When following links, one must be aware of the starting point. The script I wrote was designed to create, in a given "Ticket" record, the desired value for that record. Each time a "MTL ACTIVITY" link is dereferenced, it implicitly starts from the current record. As you correctly remarked, this cannot work as is if you need to compute the desired value for another record. To solve the problem, one does not need to actually "jump" to another record; it suffices to establish the correct context.

    To illustrate this, I added another, completely unrelated, table called, who knows why, "Dashboard". The choice field to select a Ticket resides in that table. In order to establish the needed context, the whole script is enclosed in parentheses, preceded by the designation of the chosen record.

    Side notes:

    • the "order" operation has been moved to another possible place in the script;
    • the grand total is computed inside the loop by adding the rounded values; computing and rounding it independently can give a result that does not exactly match the sum of the displayed values, which would certainly offend accountants;
    • a "Choice (dynamic)" field is limited in the number of records it can handle.
       
    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    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…

      • spinner_7580
      • 1 yr ago
      • Reported - view

      Alain Fontaine 

       

      Thank you very much Alain! -  The url bits are pretty far over my head - I'm currently using the Mac app.

      I can't quite puzzle through how the loop actually works, i.e., the logic.  I should look into the manual regarding that, I haven't attempted them other than "copying" what other sages, such as yourself, have mastered.

      It all seems to be working smoothly though - thank you!

      The next challenge is to wrap my head around the printing function.  So far it seems counterintuitive.

      Why can't we reuse views that are already picking the proper records to display?  That is, I can only print all of the records.  It woudl be nice to just plunk a view down print and be done.

      If I select a form it will only print the record that happens to be selected in the table.

      I suppose I should start another topic.... cheers!

    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    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"
    
    
      • spinner_7580
      • 1 yr ago
      • Reported - view

      Alain Fontaine 

       

      Alain - thank you very, very  much for the detailed explanation.  I will need to go thru it several time to make sure I understand.  I'm glad you explained the url-bits -- I was perplexed by that.

       

      A couple of quick questions:

      When you link a record via the"chain", for example writing from the activity table to the ingredients table to create the link.  What happens when you later delete the activity record that was linked to ingredients?  Is there some "orphaned" data in the ingredients table floating around?

      If there is more than one person accessing the database via a browser is it necessary to change the Binding parameter from the default?

       

      Thanks again Alain!

      • Alain_Fontaine
      • 1 yr ago
      • Reported - view

      From the example given, it was clear that a single "Ingredient" can be referenced by several "Activities" depending on a single ticket. What was not clear was if a given "Ingredient" can be used in different tickets. I supposed that it is possible, hence the filter I introduced to only keep the ingredients used in the current ticket.

      If all the "Activities" referencing an "Ingredient" are deleted, the record stays in the table. If need be, you can clean the table with this one-liner:

      delete select Ingredients where cnt(Activity)=0
    • Fred
    • 1 yr ago
    • Reported - view
    spinner. 7580 said:
    If there is more than one person accessing the database via a browser is it necessary to change the Binding parameter from the default?

    This is a good question and from what I see (just my two cents since I don't have any multi user DBs) is that binding comes in handy when using dashboards.

    Say you have a dashboard that people use and there is a choice field that you DO NOT want to push to other users. Then you would set the binding to be "per record in memory (browser)" so that whatever choice one user selects does not affect what other users select.

    FYI, you will also NOT see these choices show up in your DB history since they are only stored in the browser.

Content aside

  • 1 yr agoLast active
  • 10Replies
  • 262Views
  • 3 Following