Method:Classic General Q&A

Re: I need an outline how to structure these actions and loops

MikeB — Mon, 26 Aug 2013 15:03:21 GMT

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

Re: I need an outline how to structure these actions and loops

Method_Jason — Mon, 26 Aug 2013 14:48:41 GMT

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

Re: I need an outline how to structure these actions and loops

MikeB — Mon, 26 Aug 2013 14:32:19 GMT

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.

Re: I need an outline how to structure these actions and loops

MikeB — Mon, 26 Aug 2013 13:12:08 GMT

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

Re: I need an outline how to structure these actions and loops

MikeB — Mon, 26 Aug 2013 12:15:43 GMT

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.

Re: I need an outline how to structure these actions and loops

Method_Jason — Fri, 23 Aug 2013 20:09:40 GMT

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

Re: I need an outline how to structure these actions and loops

MikeB — Fri, 23 Aug 2013 18:31:14 GMT

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.

Re: I need an outline how to structure these actions and loops

MikeB — Fri, 23 Aug 2013 17:48:12 GMT

Here’s what I did:

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
Assign Value to Action Result: Create an action result called resultscript. The SQL for this is: FullName='customerfromrow' AND ActivityType <> 'Training:Certification Retail Exam'
Assign Value to Action Result: Create an action result called “resultcustomerfromrow” with the Value From Row field “Fullname” selected
Character function: Select action result “resultscript” and replace characters (type in) “customerfromrow” with the action result “resultcustomerfromrow”. Save under the same name (“resultscript”)
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”
Update field in table: Customer table, field “PDHours”, the new value will be action result “resultPDHours”, where FullName = Value from row FullName.
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.

Re: I need an outline how to structure these actions and loops

MikeB — Fri, 23 Aug 2013 17:31:17 GMT

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.

Re: I need an outline how to structure these actions and loops

Method_Valbon — Fri, 23 Aug 2013 17:00:04 GMT

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.

Re: I need an outline how to structure these actions and loops

LaCrews — Fri, 23 Aug 2013 16:45:29 GMT

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.

Re: I need an outline how to structure these actions and loops

MikeB — Fri, 23 Aug 2013 16:41:30 GMT

So I see I would do the following:

Start loop through customer table
Assign Value from action result: This will be my advanced SQL script. Each customer will use the field "customerfromrow" to identify it.
Assign Value from action result: This assigns the fullname in the row of the customer table to resultfullname)
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
Retrieve value from table: use the advanced SQL script from #2

I guess what I really need is help with #2 - the script details. I posted the options I need above.

Re: I need an outline how to structure these actions and loops

MikeB — Fri, 23 Aug 2013 16:34:43 GMT

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.

Re: I need an outline how to structure these actions and loops

Method_Valbon — Fri, 23 Aug 2013 16:22:00 GMT

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.

I need an outline how to structure these actions and loops

MikeB — Fri, 23 Aug 2013 13:06:49 GMT

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