Method Community

What I am trying to do is sum the amounts from invoices within a date range and enter them in a new field in the customer table called "aaSumofInvoices".  This is what I have done so far and the errors associated with them.  

I added a field in the customer table called “aaSumofInvoices”.

 On one of my test screens I added two date picker fields, one called “From” and one called “To”.  Then I added a button.  Under the button actions I did a simple loop through table:

            1  Start Loop through Invoice table where RecordID greater than Type In 0, and Where TxnDate Greater than or equal to Value From Screen “From” and where TxnDate Less than or equal to Value from Screen “To”.

            2  Retrieve Value From Invoice Table From Amount Field where Field = Customer_RecordID, value is: Value From Row Customer_RecordID, If multiple results Retrieve Sum and Place Value in Action Result “SumofInvoices”

            3  Update “aaSumofinvoices” Field In Customer Table, Value from Action Result “SumofInvoices” where Record ID = Value from Row Record ID.

            4  End Loop Through Table.

When I try and run exactly as above I get error message “Action Sequence:1  The field for (Value From Screen) was not found.  Please verify your actions and republish this page”.  I’m not understanding this message.  I have picked dates in the From & To fields – once I pick dates doesn’t that put those values in those fields?

So then I took out the date filters in action 1 to see if I could at least get it to run for all dates.  When I do that, I get the error message “Error:One or more fields referenced by your actions were not found on the screen.  Please make sure all actions specify a valid object on the screen and republish”.  I don’t understand this error either as I’m looping through a table and placing result in another table.  What I ultimately want to do is have this sequence run as part of running a report, so it would be an action after clicking the report button, which would not be on the same screen as the tables either.

Right now I am just testing the logic of doing this for one table, but ultimately I need to do this in a button that will generate a report, but I need to sum amounts from 3 different tables first and then use the amounts in my report.  Do I need to have all three tables on the screen where my button is? 

Any help you can offer would be so appreciated.

Thanks!!!

Karen

Hi Karen,

Sorry for the delay.  I actually ran into the same issue you had with the date range. There is however an easy fix for this.  Instead of using a "value from screen", I would first assign the date values to Action Results and then use those action results in the Where clause.  I will create a ticket for this, as you should be able to use the values directly from the screen.

Can you tell me a bit more about the second issue? I notice off the bat that action #3 might be a bit off.  You are updating based on "value from row = RecordID".  If you are looping through the Invoice table, the recordID is the Invoice RecordID, not the customer.  Could this be the source of the issue.

- Adam