Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

Summing totals with GetNthRecord

laguna92651
 Share

This topic is 3488 days old. Please don't post here. Open a new topic instead.

Recommended Posts

laguna92651 Collaborator

laguna92651

Posted
Posted

I have created a portal on a dashboard that sums invoice totals by the 7 days of the week for a specific store location. That all works fine.

DOW                       Sub Total Amt

Sunday                   $100

Monday                   $200  

Tuesday                  $150

Wednesday             $125  

Thursday                 $225

Friday                      $210

Saturday                  $170

I am summing the daily amounts in the INVOICE_SELECT_LOCATION TO by location and as mentioned get the correct results for a location fine on the portal.

I also need to chart the portal data by days of the week, the same data on the portal, I am using Google charts for charting because the native FM charting doesn't work in runtime. I have set up 7 unstored calculation fields in the Dash TO to store the sums from the portal using the GetTheNthRecord function, I then use the 7 unstored calculation fields that have the 7 daily sums in them in the Google charts html code. The GetNthRecord function does not retrieve the correct data, each of the 7 DOW_Location fields show the same total, the total for all sales for all 7 days for the selected location.

Calculation used for the sums in the Dash table.
GetNthRecord (dash_INVOICE_SELECT_LOCATION||id_DOW|::sum_subtotal_price;1 ), incrementing the by 1 for each day

Unstored Calculation, the sum_subtotal_price is the same field as on portal, which is giving the correct result.

I used the same approach on another set of similar TO's and the GetNthRecord function worked fine. However in that case the summing was done in the middle TO to sum all locations by days of the week.

Relationships between dash_DOW_SELECT_LOCATION and dash_INVOICE_SELECT_LOCATION are:

ID_Day_Of_Week = id_day_of_week
DateStart <= DateInvoice
DateEnd >= DateInvoice
Filter_ID_ProjectLocation = id_project

DOW Selector.png

eos Mentor

eos

Posted
Posted

DOW                       Sub Total Amt

Sunday                   $100

Monday                   $200  

Tuesday                  $150

Wednesday             $125  

Thursday                 $225

Friday                      $210

Saturday                  $170

Why create seven fields (or even one) if you just want to pass the values as (I assume) an array to Google Charts in a Web Viewer?

e.g., what does 

List ( dash_INVOICE_SELECT_LOCATION||id_DOW|::sum_subtotal_price )

return?

laguna92651 Collaborator

laguna92651

Posted
  • Author
Posted

Yes, that is exactly what I am doing. I am just piecing together bits of Google Charts html, so really have little knowledge of html, if this is the code I have

 function drawChart() {

        var data = google.visualization.arrayToDataTable([
          ['Day', 'Sales per Day'],
          ['Sunday',      "&DASH::go_DOW21 &"],
          ['Monday',      "& DASH::go_DOW22 &"],
          ['Tuesday', "&DASH::go_DOW23 &"],
          ['Wednesday',"&DASH::go_DOW24 &"],
          ['Thursday',"&DASH::go_DOW25 &"],
          ['Friday',"&DASH::go_DOW26 &"],
          ['Saturday',"&DASH::go_DOW27 &"]
        ]);

how would  List ( dash_INVOICE_SELECT_LOCATION||id_DOW|::sum_subtotal_price ) replace it?

The DASH::go_DOW21 is the field I am applying the GetNthRecord to.

Thanks for the help.

laguna92651 Collaborator

laguna92651

Posted
  • Author
Posted (edited)

I wasn't able to integrate the code snippet into the code I had, here is the full code that I placed in the FM web viewer to generate a column chart. I just hard coded some numbers in for the data, what you had done is exactly what I was looking for. Would you be willing to place your approach into this code? What are the tradeoffs of the two approaches? Thank you.

"data:text/html;charset=UTF-1,<html>
  <head>
    <script type=\"text/javascript\" src=\"https://www.google.com/jsapi\"></script>
    <script type=\"text/javascript\">
      google.load(\"visualization\", \"1\", {packages:[\"corechart\"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {

        var data = google.visualization.arrayToDataTable([
          ['Day', 'Sales per Day'],
          ['Sunday',      10],
          ['Monday',      20],
          ['Tuesday', 30],
          ['Wednesday',40],
          ['Thursday',50],
          ['Friday',60],
          ['Saturday',70]
        ]);

        var options = {
          title: 'Sales Per Day For Year',
           legend: { position: 'none' },
           chartArea: {left: 50}
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('piechart'));

        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id=\"piechart\" style=\"width:450px; height: 175px;\"></div>
  </body>
</html>"

ChartDaysOfWeek_eos_Modified.fmp12

Edited by laguna92651
eos Mentor

eos

Posted
Posted (edited)

What are the tradeoffs of the two approaches?

If you create a calc field in the DOW table with a pre-formatted result, you can simply List() these results – and, should it be necessary, change the format in one place. The drawback is that you have an extra field, but that shouldn't be a big deal in a utility table.

Without the calc field you need to pick the results of List() apart, which is more verbose and repetitive.

You can combine the pros of both approaches by using ExecuteSQL(); see the code in the second Web Viewer.

Also note the use of single quotes in HTML and JS; those don't have to be escaped, so the code looks cleaner.

ChartDaysOfWeek_eos.fmp12.zip

Edited by eos
laguna92651 Collaborator

laguna92651

Posted
  • Author
Posted

Neither chart displayed, I checked some other google charts I had done to be sure google was up, and they came up okay.

eos Mentor

eos

Posted
Posted (edited)

They worked when I posted the file.

Screen_Shot_2015-07-02_at_17.34.31.thumb

Do a refresh, or simply switch a location off and on again (which also performs a refresh).

Edited by eos
laguna92651 Collaborator

laguna92651

Posted
  • Author
Posted

Hmm, no success, I pasted the standalone graph I sent you and it displayed fine. Any other thoughts on what I might try?

eos Mentor

eos

Posted
Posted

Hmm, no success, I pasted the standalone graph I sent you and it displayed fine. Any other thoughts on what I might try?

Not really.

The following is what the WV calculations in my file return. What do you see when you put that directly into a WV?

"data:text/html;charset=UTF-1,<html>   <head>     <script type='text/javascript' src='https://www.google.com/jsapi'></script>     <script type='text/javascript'>       google.load('visualization', '1', {packages:['corechart']});       google.setOnLoadCallback(drawChart);       function drawChart() {          var data = google.visualization.arrayToDataTable([           ['Day', 'Sales per Day'],['Sunday',325],['Monday',450],['Tuesday',425],['Wednesday',600],['Thursday',875],['Friday',650],['Saturday',500],]);          var options = {           title: 'Sales Per Day For Week: Suburbs, Downtown',            legend: { position: 'none' },            chartArea: {left: 50}         };          var chart = new google.visualization.ColumnChart(document.getElementById('chart'));          chart.draw(data, options);       }     </script>   </head>   <body>     <div id='chart' style='width:450px; height: 175px;'></div>   </body> </html>"

eos Mentor

eos

Posted
Posted (edited)

Still blank, I noticed you are on a Mac and I am on Windows, would that cause any difference?

This is the exact same code as the one you posted, the only difference being the contents of the data array, and the use of single quotes instead of double quotes.

Since your original question has been answered, I suggest you study the examples to see how you can merge dynamically queried data with static code.

Edited by eos
laguna92651 Collaborator

laguna92651

Posted
  • Author
Posted

Okay, I appreciate the help, I will go over the examples you provided. Thank you

Can you recommend a site that I can use to learn Execute SQL?

eos Mentor

eos

Posted
Posted (edited)

Can you recommend a site that I can use to learn Execute SQL?

Maybe start here:

http://filemakerhacks.com/2012/11/02/pdf-version-of-the-missing-fm-12-executesql-reference/

Note that the first WV in my sample file does not use eSQL(), and that it definitely pays to be familiar with “native” techniques.

You would still need to combine the data gathered via SQL with other parts of your solution, so you should know your way around FileMaker's functions & script steps.

Edited by eos

This topic is 3488 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.

  I accept