Method:Classic General Q&A

Re: Retrieving distinct values from tables

Rolf — Thu, 04 Jul 2013 20:52:16 GMT

Sam,

Thanks for you efforts on this issue. I got it to work by doing my own distinct value test using a conditional within the loop.

Rolf

Re: Retrieving distinct values from tables

Method_Samuel — Thu, 04 Jul 2013 16:00:09 GMT

Hey Rolf,

Unfortunately, Method does not allow for the SQL DISTINCT keyword. Also, the value from row has to be the from the same field as the distinct option.

Based on what you've told me, you might be able to get the result you want by embedding a second loop into the first loop. (First loop gets distinct customers, and the second gets distinct locations)

For example, as a modification to my original post:

1) Step 3 is mostly the same and should retrieve distinct customers (but leave out location). Then store this result.

2) Create another loop through table directly below the first loop where customers = result from the first loop. Set the location to be distinct.

3) Add the Basic Math Calculation

4) End both of the loops

Hope this helps. If you are still having issues, try explaining what your end goal is, and we might be able to find another solution.

Sam

Re: Retrieving distinct values from tables

Rolf — Wed, 03 Jul 2013 22:52:39 GMT

Hi Sam,

Thanks for your suggestion on this. I had hoped you were going to suggest some implementation with the SQL 'Distinct' keyword with my Action Result to achieve this.

As to your suggested approach, I had already tried something like that. The problem is that not only do I need to count distinct customers, but I need to do this for multiple locations. Unfortuneatly I am one 'where condition' short in the 'loop through table' command. So, what I did was in that loop, I put in a standard conditional statement, and if the location identifier ( which I already had added to the Invoice Line table) matched the location I wanted, then I incremented my counter.

What I found was that when I did not select 'distinct' in the 'loop through table' command, this all worked fine and I was able to count the total number of customer records per location this way, as I would expect, but of course I counted the customers multiple times. When I set the loop to 'distinct Customer' , then the looping function failed in retrieving the 'location' value, and allocated all locations to the value of the first location alphabetically. Somehow the 'distinct' option cause the 'value from row' fetch of the location to be incorrect. I even tried doing a 'retrieve value from table with the current record ID' in the loop, which also failed. (although I think that was because it's not allowed in the loop).

I know this explanation seems convoluted, so I'll give an example to hopefully make it clearer. For the time period I was checking, I had four customers from two locations. Chronologically, they were Langford, Victoria, Langford, Langford. They all returned Langford as the location. Only when I changed the time period to only include the one record from Victoria, did I get that returned as the location.

One further bit of info. When I used the 'value from row' in the conditional command, it created an error if the 'loop through table' was distinct, but not if it wasn't I got around this by assinging an action result to the value in the loop, and using the action result in the conditional command. At first I thought this was a bug in the program, but maybe it's a feature if other fields are not available in 'distinct' mode, but then it should have failed on the action result assignment.

Any thoughts on how to proceed from here ?

Rolf

Re: Retrieving distinct values from tables

Method_Samuel — Wed, 03 Jul 2013 21:03:08 GMT

Hi Rolf,

To accomplish what you are suggesting you need to do a few things.

1) First, you must add a linked field to the InvoiceLine. Under Customize -> Tables -> InvoiceLine click edit fields. Select Linked Field from the radio button. Choose the Invoice table under Link Using, and select Customer field under Linked Field. Click on Finished Editing Fields.

2) Now that Customer is linked to InvoiceLine, you can create a set of actions to count the number of distinct customers. Initialize an empty action result by adding Assign Value to Action Result, name the action result and give it the value 'Type In' blank or 0.

3) Create a loop through the InvoiceLine table by adding the action Start Loop Through Table. Select the criteria for the loop to filter through, and then select Customer under the Only distinct values dropdown. Click Save & Close.

4) Select Basic Math Calculation from the actions list. Select the action result from step 1, and add (+) 1. Store the result to the same action result name. Click Save & Close.

5) Add End Loop Through Table

6) Put in an action to give you the final result. This could be a popup, or it could populate a field on the screen.

Hope that helps.

Sam

Retrieving distinct values from tables

Rolf — Tue, 02 Jul 2013 17:15:40 GMT

Hi,

I'm retrieving summary data from the InvoiceLine table by using the retrieve from table command, with search criteria specified in an action result, and then using the sum and count options for the rresults. This retrieves the correct values, but I would also like to know the number of distinct customers involved in these transactions. Any suggestions on how I might do that?

thanks,

Rolf