Method Community

I want to have a button (stand-alone, not on any screen) that when I press it, it does the following:

• For every Record ID in the customer table
• Loop through the activity table and add up the duration hours for each Record ID in the customer table for a predefined time period
• Populate that duration number into the customer table for each Record ID

It seems like it would be simple to do, but I've tried a framework of:

• Loop through customer table
• Add specifications (eg. If Is Active = TRUE)
• Loop through Invoice table
• Sum up duration hours
• Update record in customer table
• End Loop
• End loop through customer table

But this doesn't seem to work. Is this the correct way to go about it? Can we have loops within loops? Do I need to place the results of looping through the invoice table into an action result that holds the sum temporarily for each record?

Thanks

Hi Mike,

If I understand correctly I'm going to offer a much better, more efficient and simple approach to this. Rather than having nested loops (loops within loops) you can use another approach.

1. Loop through the customer table OR loop through grid for only checked rows
2. Retrieve Value From table

• Retrieve from Activity table
• The field is likely ActualDuration (or whatever field you use)
• Where the "Entity" = Value From Row "Name" 
• Use "Retrieve Sum" if multiple results option
• Store as an action result

3. Update Field in Table (field in your customer table)
4. End Loop Table/Grid

Magic! You are now only looping through one table and very easily able to retrieve the total value you are looking for. 

Hope this helps.

This is what I did between my post and your response. My problem now is that I need to use an advanced SQL script. I've watched the webinar and I think I get how to assign the script to an action result and then use the script in the WHERE part of the Retrieve Value action.

My problem now is doing the script (I'm not experienced SQL)

What I want is the following: WHERE

• Activity type is NOT a certain type (eg. Meeting)
• Activity type is NOT another type (eg. Phonecall)
• Activity end date is between 2 start and end dates. These start and end dates are defined in the actions of the button and are stored as action results DateStart and DateEnd, let's say.

So I see I would do the following:

1. Start loop through customer table
2. Assign Value from action result: This will be my advanced SQL script. Each customer will use the field "customerfromrow" to identify it.
3. Assign Value from action result: This assigns the fullname in the row of the customer table to resultfullname)
4. Character function: This replaces the "customerfromrow" field in #2 with the "resulfullname" field in 3 so that we can use each customer record in the loop
5. Retrieve value from table: use the advanced SQL script from #2

Mike

Here's a great place to start for learning SQL.

http://www.w3schools.com/sql/sql_where.asp

Also, you can achieve the same result with the report designer.

Mark - nice suggestion using the report designer.

Mike - not sure on your exact use case but if you didn't have to loop all the customers at once there's a fairly easy approach to do this one customer at a time. Filter the activity grid to your exact specifications, even use different filter views if needed. Then, use the "Get Sum" action to provide the total based on the grid's column.

This is what I did between my post and your response. My problem now is that I need to use an advanced SQL script. I've watched the webinar and I think I get how to assign the script to an action result and then use the script in the WHERE part of the Retrieve Value action.

My problem now is doing the script (I'm not experienced SQL)

What I want is the following: WHERE

• Activity type is NOT a certain type (eg. Meeting)
• Activity type is NOT another type (eg. Phonecall)
• Activity end date is between 2 start and end dates. These start and end dates are defined in the actions of the button and are stored as action results DateStart and DateEnd, let's say.

Here’s what I did:

1. Loop through customer table where FullName starts with “Renu”. I want to work with a small batch of fullnames first. I can remove this once I know this works
2. Assign Value to Action Result:  Create an action result called resultscript. The SQL for this is: FullName='customerfromrow' AND ActivityType <> 'Training:Certification Retail Exam'
3. Assign Value to Action Result:  Create an action result called “resultcustomerfromrow” with the Value From Row field “Fullname” selected
4. Character function: Select action result “resultscript” and replace characters (type in) “customerfromrow” with the action result “resultcustomerfromrow”. Save under the same name (“resultscript”)
5. Retrieve value from table: Activity table, field “ActualDuration”, where advanced script is the action result “resultscript”. Retrieve sum and place value in action result “resultPDHours”
6. Update field in table: Customer table, field “PDHours”, the new value will be action result “resultPDHours”, where FullName = Value from row FullName.
7. End loop through table.

There are no errors, but no values are being populated.

Any thoughts on what’s wrong? I’m really close, I think.

No thoughts yet? I'd like to know where I'm going wrong - it'd be great to have this done before the weekend. Again, I think I'm pretty close.

Hi Mike,

I'll jump in. I'm not sure where exactly the problem is, but it may help to use a Conditional Warning message that is always true for troubleshooting purposes. You can put the values your trying to create into the message to make sure they are being generated correctly when you press the button. This will help you to find out where exactly your action set is going wrong. Hope this helps. 

Jason

Thanks Jason, I've been using the "Stop Processing all actions". I've tested this where I've looped through the customer table, but I've specified a single RecordID, so the retrieve value portion of my actions should only pull one record and populate it onto a test field on the screen. But this isn't happening, and I'm thinking there's something wrong with my script syntax, or how the dynamic variable ("resultcustomerfromrow") is used to retrieve data for each row of the table. 

OK, so things are partially working now. I discovered a typo in referring to the dynamic variable used to represent the customer full name in the loop. Simple retrieval and population works now for more than one customer at a time.

But I'm having trouble adding firther defining parameters to the script. In retrieving the variable "ActualDuration" from the Activity table, the basic script that works is: FullName = 'customerfromrow'

I try to add further specification:

FullName = 'customerfromrow' AND ActivityType = 'Admin:Contact (e-mail/phone)'

but I end up getting the same results as the script above.

Any thoughts on why my additional specification regarding Activity Type is not being recognized?

Thanks

Well, I used the square brackets to surround the fields and that seems to work.

My final issue is this:

How do I show results that are within certain dates. The following script works for me EXCEPT for the date part:

[FullName]='customerfromrow' AND [ActivityType]<>'Admin:Active Selling Status' AND DueDateEnd < 2013/08/15

DueDateEnd is in the Activity table, and I'd like to replace the hard-coded date with an action result, but...baby steps.

Hi Mike, 

I'm glad it's coming along! Take a look at this article. It talks about scripts you can use to filter the Export tool, but I believe similar scripts can be applied in your scenario. There are also some date filters that may be useful. 

Jason

Thanks again. And by the way, some of my earlier issues were due to the fact that the action result script that I was referring to in the Retrieve Value From Table action was replaced by a different action result for some reason, and I was unaware of it.

I have seen that help section before but I forgot about it.

Thanks