Method Community

Both Loop Through Table and Loop Through Grid have a property that allows selection of a field for "Only show distinct values for field" in the case of Loop Through Grid, or "Only distinct values (optional):" in the case of Loop Through Table.

What's a "distinct value"?  There's no explanation in the Actions Guilde/Online User Guide.

Is this a way to avoid duplicates?  How is this used?

Also, Loop Through Table allows a selection of Where Clause operators like Equal To, Less Than, Greater than, etc. but the Loop Through Grid action only allows the use of Equal To in the Where Clause.

Being able to use the full selection of boolean operators could be very handy in Loop Through Grid.  Is this under consideration for future functionality?

Cheers,

James 

jnoneiliv1:

What's a "distinct value"?  There's no explanation in the Actions Guilde/Online User Guide.

A distinct value is the same as a unique value.  If you wanted to selectively loop through records by a distinct value then you would use this option.

jnoneiliv1:

Is this a way to avoid duplicates?  How is this used?

Yes.  The use of this would be a specific case, maybe you wanted to delete a bunch of leads from a table by distinct value email.

jnoneiliv1:

Being able to use the full selection of boolean operators could be very handy in Loop Through Grid.  Is this under consideration for future functionality?

I agree.  I'm not sure if this is under consideration at the moment but I'll post here if so.

I have to caution you on the use of the loop through grid on a distinct value.  In testing your question this morning it started to produce inconsistent results for me.  I'm going to continue testing and post and update but I would recommended you don't use a loop through grid with a distinct value right now.

~C

Chad,

I think answering this question in terms of typical SQL statement logic might help clear things up. As I was still lost after your explanation I looked up an explanation of the SQL SELECT DISTINCT Statement.

The SQL "DISTINCT" keyword will prevent inclusion of records that have duplicate values in the named field or column.  This is still a bit confusing since typically SQL Select Distinct might return unique values, but in the case of a Loop Through Table we are really talking about entire records that the Action will Loop through.  So, we still need some explanation as to how this impacts record processing.

I could see how this could be used to filter the records selected by the Loop Through Table Action and I assume that if the properties for Sorting the records are configured in the Action properties that the DISTINCT option would select the first record from a group of duplicates as determined by the sort critieria?  This could be very handy to grab the most recent record for example the last invoice issued a customer.

How would this work for the Loop Through Grid Action since sort options are not available?  Would it choose the first physical record in a series of dups?

I think if we can try to relate the logic back to SQL and then explain how it might be a little different for how the Actions are using it we might have a shared language for defining the logic and how it might be used in a way we can all relate.

I get lost by the use of conversational english used in the how to guides.  I'm not sure it's helpful to avoid specific technical language and I think some grounding in basic relational database functionality is required to really develop custom action sequences with even moderate complexity.

Cheers,

James

James,

jnoneiliv1:

I think answering this question in terms of typical SQL statement logic might help clear things up.

While it's helpful to answer in terms of SQL statement logic, we're not writing a SQL statement so I wanted to try and explain what distinct means outside a SQL statement.

jnoneiliv1:

This is still a bit confusing since typically SQL Select Distinct might return unique values, but in the case of a Loop Through Table we are really talking about entire records that the Action will Loop through.

Not might, Distinct will always return Distinct (aka unique records in non-SQL language).

jnoneiliv1:

So, we still need some explanation as to how this impacts record processing.

With regards to record processing, if you're looping through the table with a distinct by, you're only going to see records that match that distinct clause.  It impacts your processing by looping through distinct (by) records only.

jnoneiliv1:

...if the properties for Sorting the records are configured in the Action properties then the DISTINCT option would select the first record from a group of duplicates as determined by the sort criteria?  This could be very handy to grab the most recent record for example the last invoice issued a customer.

When Looping Through a Table your Distinct clause has an optional Order By condition.  Without the Order By condition then it would Order them By entry in the table.  The sort criteria you mentioned only applies to a the Loop Through Grid action and yes, it should pull the first record.

jnoneiliv1:

How would this work for the Loop Through Grid Action since sort options are not available?  Would it choose the first physical record in a series of dups?

Loop Through Grid by Distinct was producing inconsistent results which has been rectified as of the April version.  If you need to use the Loop Through Grid with a Distinct value then please log out/in, switch to the latest version of Method and republish your screen.

jnoneiliv1:

I think if we can try to relate the logic back to SQL and then explain how it might be a little different for how the Actions are using it we might have a shared language for defining the logic and how it might be used in a way we can all relate.

I try to answer these posts in the best technical and non-technical terms depending on the question.  Since we're not writing a SQL statement in the Loop Through actions I wanted to make it clear that Distinct in this instance means unique (which is non-SQL programmer friendly).

jnoneiliv1:

...I think some grounding in basic relational database functionality is required to really develop custom action sequences with even moderate complexity.

I disagree with this statement.  While technical knowledge is definitely helpful to developing custom action sequences, it's not required.  We built Method to help non-technical folks develop business processes outside the bounds of QB while still allowing them to use QB as their accounting program.  If you feel the language in the manual is too conversational then I'll discuss it with the team here.  We're trying to balance technical and non-technical folks alike.

~C

Thanks Chad, that clears things up.

I think we are writing an SQL statement though, you've just hidden the ugly bits from the user, made a nice gui to allow the user to build a statement by selecting a few key properties, fields, etc. and then written some code to parse that into whatever SQL language, object oriented, ASP whatever.

Do you think it's just a coincidence that you are using terms like "Where clause", "Select", "Distinct", etc.?

Anyway, if you just copied and pasted some your explanation to the User Guide, we'd have some nice additional explanation of the Loop Actions, and thanks a bunch for characterizing the Loop Through Grid Action.

Oh, and as side note.  You might add as a usage example that the Loop Through Table Action is a really great tool for updating a bunch of fields even on just one record.  I finally figured this out looking at some sample screens, and now I wish I had time to go back and replace bunches of separate Update Field in Table Actions with one Loop Through Table Action operating on a single current RecordID.  It's got to be much more efficient in the database as well and speed execution.

Cheers,

James 

jnoneiliv1:

Do you think it's just a coincidence that you are using terms like "Where clause", "Select", "Distinct", etc.?

No coincidence and all those terms are all applicable - Distinct is the only one I always try to refer to as unique outside SQL statements. 

jnoneiliv1:

You might add as a usage example that the Loop Through Table Action...

Great Stuff!

It's pretty amazing how powerful the Action library is for making custom applications.  The balance of functionality and ease of use ir really elegant, and I think some further explanation of usage would be a good investment.

I can't think of a more powerful toolkit for small business than QB plus Method.  We're using it in our iron foundry, and I don't think there's a better solution even for us.

Cheers,

James