Corresponding multiple values and sort

[LaRetta]

Attached is a sample of the issue.  I have a list of IDs (gathered by the new summary option of ListOf in v13).  I need to take this list (yellow) and convert them to their description value, produce a unique set of results, sort them and put them in a concatenated string as:

 

Apples, Cherries, Grapes

 

I can use two value lists (one with the IDs and the other with the values ... neither of those fields are ever empty) and I can find the corresponding value to a single ID.  And I can even take a text list and sort them against the value list using FilterValues().  And of course turning a list of values into a string is easy also.

 

But how to handle many values, remove duplicates and sort and put it together?  I would rather not add another relationship to the graph but I will if need be.  

 

Ideas please?

testValues.fp7.zip

[LaRetta]

BTW, I can't use a relationship.  These are values displayed in a leading part based upon GetSummary().  The report is already sorted and must remain sorted in its existing order which is NOT the same order as the list of IDs.

[eos]

Attached is a sample of the issue.  I have a list of IDs (gathered by the new summary option of ListOf in v13).

 

But your sample file is fp7 …!? If you do have v13, I'd suggest trying ExecuteSQL ( "SELECT DISTINCT ( textField ) FROM someTable WHERE someID IS IN (" & Substitute ( yourIDList ; ¶ ; "," ) & ") ORDER BY 1" ; "" ; "" )

[LaRetta]

Thank you, Oliver!  However, I do not want to use ExecuteSQL() in a calculation.

 

The reason I used an fp7 for the example is because the functionality would work regardless how the list of IDs came about.  Taking advantage of the value lists (optimised in 13) is more efficient, I should think.

 

Thanks for the example - I can use it elsewhere I'm sure! 

[LaRetta]

Well this works.  And it would work with Michael's cf also but I did not want to adjust his.  Still ... it feels a bit rough but then it always does.

I also think the FilterValues() should be an option in the cf to sort or not.  I didn't concatenate into a string because I feel that should happen in the calc itself and not necessarily in the cf.  Fun stuff.

 

updated:  I added the FilterValues() into the cf.

testValuesMOD.fp7.zip

[eos]

I don't see why you cannot use a relationship – unless I misread your requirements. See attached.

testValues_eosMOD.fmp12.zip

[LaRetta]

But you ARE missing the requirements.  The report is already sorted in a different order and must be maintained in that order.  Looks nice though.

 

Added:  In addition, it is based upon a found, grouped set.

[eos]

But you ARE missing the requirements.  The report is already sorted in a different order and must be maintained in that order.  Looks nice though.

 

Added:  In addition, it is based upon a found, grouped set.

 

I don't understand how that is different from my grouped, sorted (by another field) found set that keeps its order while generating one string per sub-summary, consisting of unique, sorted values.

Why don't you create a sample that shows how it is supposed to look?

[LaRetta]

One would think that after 12 years in this business, and knowing I checked your demo, that if it would work at all, I would at least tell you and that I would know the difference?

 

 I do not plan to create a demo of this highly complex situation which is why I posted the file I posted.  If you wish to address the situation without sort or relationship or ExecuteSQL(), I will listen further.  Have you looked at my CF?  It ends up having a fairly light footprint.

 

Thank you anyway.

[comment]

 I need to take this list (yellow) and convert them to their description value, produce a unique set of results

 

Could you clarify two things?

 

1. I don't see that there are any duplicates in the yellow list to begin with.

2. Sort them by what order?

[LaRetta]

1. I thought there would be duplicates in the yellow field but no, there would be no duplicates because each row (run)  is * unique!  So I guess I only need to convert them from the id to the Abbreviation then sort.
2. I would like to sort alpha on the result of #1 which would be text (Abbreviation)

Ah.  By the Abbreviation of the report, this Abbreviation would not have duplicates in the body (OppositeEnd::PanelID).  Both ID and Abbreviation are unique in their table (which is two tables away).  Without need to remove duplicates, it may be simpler still.   Many approaches here, for sure.  With Value Lists being handled by FMS instead of client, they are move valuable.  I've used VL of ID then VL of name for translating before but is it worth it here?

 

I want this file as uncluttered as possible.  The record sets and file complexity will always remain very small (under 500 records) and the set of Ids to condense (in the yellow field) with probably never exceed 25.  <--- this is WAG.  It would be max connectors to a TP I think.

 

* Each row is unique to that grouped set, I mean - not unique in the table.  It is the lowest level break field.  ​  No sorry, the field included in this sort and is not a break field.

  

Added red

changed 'name' to 'Abbreviation' for clarity

Edited March 28, 2014 by LaRetta

[LaRetta]

I have the Cable Numbers condensed using your CollapseList()  http://www.briandunning.com/cf/734

 

They are assigned in serial order.

[comment]

If you translate them and then filter them against value list items of the textual VL, it will sort them AND remove the duplicates (if there were any). I was going to suggest you start by filtering them against the ID value list to remove any duplicates first, because the translation is the more intensive process here - the filtering will be instant, IMHO.

 

--

I did not understand the parts you added after your clarifications. They do not seem to  be related to the file attached earlier or to the question as posed.

[LaRetta]

Hi Michael, thank you!

 

Filtering first sure makes sense particularly with larger value lists but I have been unable to see how to pull it off.

 

Since this approach is based upon translating an ID to its text counter-part in same position in another list, filtering the Ids first to remove duplicates will change their position relative to the text value list. We would need to filter the text value list according to the same found IDs (based upon the IDs duplicates not the text) but to do that, we need to know the IDs' position in the text VL to remove its text counterpart and we will not know that until after we translate. Whew!  It feels circular.  

 

If you think filtering the IDs first might be good to understand and/or if you could provide a bit of principle or example, it would be really great.  Or do you already have CF which can handle it?  SubstituteValues() is closest but not quite and I don't feel right modifying someone else's CF.  The method I used is based upon the same position in two lists.  Are there other ways to filter before translating other than relationship?  I tried various configurations of value lists - using the Text the second field of the ID ... nothing provided me with ability to filter both in the same positions.

 

I enjoyed working on it - thanks, guys!  And I have much yet to learn!    

 

Edited some sentences

Edited March 30, 2014 by LaRetta

[comment]

I am not sure we are on the same page here. Let us have two value lists (of the same table, so that they are corresponding):

 

IDs

1 [Orange]
2 [Apple]
3 [Pear]
4 [Cherry]

Names

Apple [2]
Cherry [4]
Orange [1]
Pear [3]

Given the following input:

2
2
4
1
4
2
1
1
4

we would start by:

FilterValues ( ValueListItems ( Get (FileName) ; "IDs" ) ; Input )

to get:

1
2
4

This reduces the number of required conversions from 9 to 3. After applying the custom function, you'll have:

Orange
Apple
Cherry

and now you only need to sort them by:

FilterValues ( ValueListItems ( Get (FileName) ; "Names" ) ; CF_Result )

If the input contains no duplicates, the first step is redundant. If the input does contain duplicates, and you skip the first step, then the duplicates will be removed anyway in the last step. But the custom function will have to work harder.

[LaRetta]

W.O.W.  What a great explanation.  I had a parameter wrong; good to know I had the theory right but it failed repeatedly so I doubted it.

//  TranslateValues ( idList ; values ; textList )

FilterValues ( textList ;  

Let ( [
deDup = FilterValues ( idList ; values ) ;
value = GetValue ( deDup ; 1 ) ;
pos = Position ( idList ; value ; 1 ; 1 ) ;
num = ValueCount ( Left ( idList ; pos ) ) ;
result = GetValue ( textList ; num ) 
] ;
result & 
Case ( values ; ¶ & TranslateValues ( idList ; RightValues ( idList; ValueCount ( values ) - 1 ) ; textList ) )
)
)

What a great way to end my night ... with a fun calculation!  Thank you for taking the time to present that!

Edited:  I had switch the parameters around (to better match other CFs which placed the searchValue into the centre spot but I had neglected to switch it everywhere, LOL.  Anyone please point out better ones too.

[comment]

IMHO, you should take both FilterValues() out of the CF and move them to the calling calculation field. There are two reasons for this, an ideological one and a practical one.

 

The ideological reason is that a function should do one thing only. You named the function TranslateValues(), but truly it should be named DedupeInputTranslateValuesAndSortTheOutput().

 

The practical reason is that the function is recursive - but both de-duping the input and sorting the output need to be done exactly once.

[LaRetta]

   

 

All I wanted was a simple Swiss Army Knife for values and I was having trouble pinning down its name for sure.  I do not like custom functions which depend upon other custom functions.  And I did not want a parameter to sort, or flatten to comma-delimited or 'name your delimiter'.  Yep, the wrapping with FilterValues() felt off.  

 

Ah.  The practical one ... I never considered that at all.  That was part of my struggle - how to include the FilterValues() down within the calculation but only once!

DedupeInputTranslateValuesAndSortTheOutput().

 

that name still cracks me up until my fingers won't work