Method Community

I am trying to retrieve values from a table for a specific date range.  I don't see any way to do this without using an Advanced Script, but I have no idea how to write one or WHERE I go to write it.  I've looked at help articles and googled SQL WHERE, and I kind of see what I need to do, but where in Method do I do it, and what is the correct syntax?  What I need it to do is retrieve Amounts from the Charges table where the Customer_RecordID is the same as the RecordID in the Customer table (which I am looping through).  I need to retrieve the sum of charges in a specified time period that I already have in Action results.  Can someone help me with this please?  

Thanks!

Karen

Hi Karen, 

There is an option to use an advanced script right in the Retrieve Value from Table action. You might also be able to do this with a regular script by using the Where Field. See my screenshot below. 

Also, here is an article with some simple SQL scripts from our Help Center. Included is some options for date filtering. If you're using an advanced script, you will need to put the SQL script into a action result in order to choose it in your Retrieve Value from Table action. Let me know if this works for you or you still need some help and I'll be glad to look into it further! 

Jason

Hey Jason,

The first suggestion won't work #1 because I'm trying to filter on transaction date, not created date, and two, because there is no way to tell it to find transactions BETWEEN two dates in the regular script.  That's been my problem from the start.

As far as the second suggestion, as I said originally, I have looked at this article already - I kind of get it, but what I don't understand and isn't clear is HOW to put it into an action result??? How & where do I do that?  within the actions I'm trying to write, or somewhere else?  or where?  Additionally, if I want the advanced script to use dates that are defined on the screen, how do I do that in SQL language?  I see where I can hard code dates in, so in my case if I wanted to only pull transaction amounts from July transactions my script would look like:

TxnDate BETWEEN '2013-07-01' AND '2013-07-31".  Is that correct?

But if I wanted the dates to come from user input, how do I write that into the script? 

Thanks!

Hi Karen, 

You can use the Assign Value to Action Result action to assign a script to an action result. I tested this and found that this format will work:

Now that the script is assigned, you can use it as your Where Script in your Retrieve Value from Table action. Use the advanced script option and choose the action result that contains the script. My screenshot below is an example I used for the Invoice table, but you can change it for use with other tables if you wish.

I'll keep working on this to see if I can get it to work with user inputted values on screen. Stay tuned! :)

Jason

Hey Jason,

Thanks so much for showing me how and where to do the scripts!  I have one little wrinkle to this and I don't know how to write that part - I really need a two part script - The button that I am trying to write actions for does the following:

1 Start Loop through Customer Table

2 Retrieve "Amount" from Charge Table where Customer_RecordID = value from row RecordID, retrieve sum if multiple results, and place value in action result.

3 Update TotalStmtCharges field in Customer table with action result value from #2, where the RecordID = value from row RecordID.

4 End Loop through table.

The above is what it is doing now - and this is returning totals for each customer for ALL statement charges to date.  So this is working fine for what it is doing, but in addition to retrieving the amount by customer, I need it to only retrieve amounts where the transaction date is between two dates (user entered, eventually).  That is where I have to bring in the advanced script.  So in the script in addition to the date filter that you have shown me above, I also need it to say where the Customer_RecordID = RecordID in the row of the customer table.  How do I add that part in to the advanced script?

PS - can't wait to see how to get it to work with user inputted values! :)

Karen

PPS You are awesome! :)

Karen,

Filtering between user inputted dates is a little bit trickier, but doable. You'll need to assign your user inputted dates to action results, and then use a combination of Date Function and Character Function actions in order to insert the date values (in the correct format) into the final script which will be used in the Retrieve Value from Table action. 

For example, if you have a Date field on screen called StartDate, use a Date Function action to change the date format to yyyy-MMM-dd. Then use a Character Function action to replace the value in your original script with the action result from your Date Function action. Now you have the user inputted date in the correct format in your script. My screenshot below is of one of my Character Functions. Note that 'StartDate' is what I'm replacing in the original script. 

I haven't tested out the Sum function yet, I'll take a look today!

Jason

Karen,

You can use both an Advanced Script and a Regular Script inside your Retrieve Value from Table action. It's a little confusing how to do so. Here are the steps how:

1. Click the Use Regular Script option 
2. Click And Where to set up a second Where clause
3. Choose Use Advanced Script
4. Now you can click And Where to add a regular script to your advanced script  

In my screenshot you'll see I have an advanced script and also a regular script. I tested the Retrieve Sum function and it works great! It only summed based on my filter criteria. Hope this helps!

Jason

Jason,

I think I was able to do this correctly, after the initial add, I can't get the screen to show both criteria at the same time like you have above, but I think the second part is still there as I have the "remove Where" option, but no "And Where" option.  However, when I run the actions, it is returning the same sum for each customer and the amount looks like the total of ALL charges for the month, so something is not working right.  Any ideas?  Help?

Thanks!

Karen

Hi Karen, 

My apologies but it turns out you cannot use an Advanced Script and a Regular Script on a Retrieve Value from Table action at the same time. What I saw before must have been a caching issue that allowed me to view both on the screen, but after more testing today I found that it will not work properly. You must add your additional filters into the original advanced script.

This is getting too advanced for the scope of the forums, but I do believe it is possible to use 'where Customer_RecordID equals value from row RecordID' inside an advanced script. You may need to use some more Character Function actions though in order to implement it properly. I'll let you know if I can come up with a solution, but it may help to get in touch with an in-house consultant or a Method Solution Provider.

Jason