Hi,

A customer needed to "List" only if the item was "Different", if more than the same item, the customer only wanted to see it "Once".  As I pushed the value list to his field, I noticed that it was not in the correct order.

Is there any way to list within a value list "creation order", the value list organizes itself either alphabetically / numerically.

I need creation order ( M43, C167, S26, G6789 etc ... ), suggestions ?

Thank you.

I tried ordering within the "Value's" relationship by my "Creation DateTime Stamp" but the value list still ordered incorrectly ...

 

The value list needs to organized this way ... creation order;

1 x  4 PG 2 UP WT 4 Out

3 x 6 PG SW 2

1 x 12 PG 1 Out SW 2

According to the documentation, value lists, if they are created from a field that is of type 'Text', then :-

9. If you want to display the values that are in a text field in a specific language’s dictionary sort order, select Re-sort values based on and choose a language. Otherwise, the values are sorted in the index order of the field’s default language. This setting is ignored for other types of fields.

See https://fmhelp.filemaker.com/help/15/fmp/en/#page/FMP_Help%2Fvalue-lists.html%23

If the value list is based on a field that is not of type 'Text', then the documentation is non-specific.

You can also choose to show a second field, and sort based on either the second or first field. Perhaps you need to manipulate the field upon which your value list is defined to be of type text, and make sure the values will sort in the order you require based on the English language?

Edited March 13, 20178 yr by rwoods

Hmm ... so I have a "Text" field in "English".

"You can also choose to show a second field, and sort based on either the second or first field. Perhaps you need to manipulate the field upon which your value list is defined to be of type text, and make sure the values will sort in the order you require based on the English language?"

I don't display the value list, only use the value list to "List" into a field via a script.  It tried to sort within the relationship but it didn't work.

The "Key" is what is in the "field record" if it's the same as other records the customer only want to see it once ... just like a value list ... lt list same items only once. I just need to gain control of the sort if possible.

Let me try this second field with the "Creation Date Timestamp" ... will get back here ...

 

11 minutes ago, Mr. Ignoramus said:

I don't display the value list, only use the value list to "List" into a field via a script.

Could you elaborate on this? If you don't need the value list as value list, then why ask about it? Ask about the real problem you want to solve.

Hi,

Sure ...

I provide a quoting table, each record carries a field that the customer enter in a value based off of a value list ... lets say it's value list "A" ( 100 choices )

Each quote averages 16 records.  And each of the 16 records are grouped by "Section Name"

I have a layout were we view different part of the quote at a glance; what I call the "Summary" layout ( not summary fields ), and on this particular layout the custom want to see a field; let say "PRINT INFORMATION" field, the customer wants to "List" the data that is grouped from group "c" for each record ... 

record 2 - Zz - 1 Strawberry

record 5 - Cc - 132 Blueberry

record 8 - Gg - 324 Banana

record 9 - Aa - 324 Banana 

record 13 - Hh - 56 Apple

record 12 - Hh - 56 Apple

record 16 - Hh 56 Apple

I built an SQL to look at the group of record and I pushed the list to his "Print Information" field on his summary layout, worked great;

Zz - 1 Strawberry

Cc - 132 Blueberry

Aa - 324 Banana

Aa - 324 Banana 

Hh - 56 Apple

Hh - 56 Apple

Hh 56 Apple

However, my customer wants to see only "Banana and Apple" once. So I built a relationship and a new value list and produced the list via a script step and it worked great ...

Cc - 132 Blueberry

Gg - 324 Banana

Hh - 56 Apple

Zz - 1 Strawberry

However, the value list sorted incorrectly for my customer.  My customer is not interested in "Alphabetic or Numeric" sorting, my customer want the "List" the way it was entered;

Zz - 1 Strawberry

Cc - 132 Blueberry

Gg - 324 Banana

Hh - 56 Apple

I hope this helps ... please keep me posted if I can be of further assistance.

Thank you.

I am afraid that's too confusing for me. I couldn't figure out what your starting point is.

Let me give you this example: suppose you have a found set of 7 records, shown in creation order (i.e. unsorted). And suppose there is a Category field that contains these values for the current found set:

Strawberry
Blueberry
Banana
Banana
Apple
Apple
Apple

Now, a summary field defined as List of [ Category ] will get you a list of the above values, in the same order. Another way to get the same list is to loop among the found records and collect them into a variable.

To remove duplicate values from this list, while keeping the order in which they first appear in the unordered found set, you can use a custom function. Or, if you're doing this in a script, you could let the script do the same thing - for example:

Set Variable [ $listOfValues; Value:YourTable::sListOfCategory ]
Loop
  Set Variable [ $item; Value:GetValue ( $listOfValues ; 1 ) ]
  Set Variable [ $uniqueList; Value:List ( $uniqueList; $item ) ]
  Set Variable [ $listOfValues; Value:Let (
xcl = Substitute ( ¶ & $listOfValues & ¶ ; [ ¶ ; "¶¶" ] ; [ ¶ & $item & ¶ ; "" ] ; [ "¶¶" ; ¶ ] )
;
FilterValues ( xcl ; $listOfValues )
) ]
  Exit Loop If [ not ValueCount ( $listOfValues ) ]
End Loop

At the end of the loop, the $uniqueList variable will contain:

Strawberry
Blueberry
Banana
Apple

---

P.S.

I found this especially confusing:

1 hour ago, Mr. Ignoramus said:

I built an SQL to look at the group of record and I pushed the list to his "Print Information" field on his summary layout, worked great;

I am not sure how exactly you use SQL to get information from the current found set. But if you know how to do that, then why can't you tell that same SQL expression to select only distinct values?

 

 

 

Hi,

Ok then, here is the "SQL" I wrote that produced the "List" the customer didn't like; meaning ... multiple like items.

ExecuteSQL ( 

"Select Get_Record_Number, Ink_Description_Litho

From SecSub

Where A_fk_RFQ_ID = ? and Line_Item_Code_Mgmt_Litho = ? " 

; " - " ; "¶" ; SecSub::A_fk_RFQ_ID ; 5 ) & "¶"

If this SQL can be filtered to "List" out only records that carry a different "Ink Description" ... how would I do that ??

I added the "Get_Record_Number" because the customer wanted to see a 1, 2, 3, 4 next to the "Ink Description" on the list.

Thank you.

So what happens if you do:

SELECT DISTINCT Ink_Description_Litho 
...

I am afraid I don't know how to number the rows when using DISTINCT. Not in SQL, that is.

Let me try this and I will be back ...

The "Distinct" changes the sort;

1st image - without "Distinct" the "List" stays by creation order, but we list the first two items twice.

2nd image with "Distinct" for some reason, the "List" sorts and we get the "List" but incorrect order.

Anyway, if there is any guidance here I would be grateful.

Thank you.

Edited March 13, 20178 yr by Mr. Ignoramus

I wasn't able to reproduce your results, but that was using the BaseElements  plugin, not Filemaker's native ExecuteSQL() function.

In any case, I gave you an alternative method.

On 3/13/2017 at 4:24 PM, Mr. Ignoramus said:

The "Distinct" changes the sort;

1st image - without "Distinct" the "List" stays by creation order, but we list the first two items twice.

2nd image with "Distinct" for some reason, the "List" sorts and we get the "List" but incorrect order.

Anyway, if there is any guidance here I would be grateful.

Thank you.

I'm having a hard time following overall what you are doing based on your previous posts.  Perhaps a sample file would be beneficial.

As for a particular order for the DISTINCT, if you have a serial ID and ORDER BY that field then it should sort by the order that the records were created.

For example using you example...

recID     fruit

2 - Strawberry

5 - Blueberry

8 - Banana

9 - Banana 

13 - Apple

12 - Apple

16 - Apple

 

ExecuteSQL ( "SELECT DISTINCT fruit FROM yourtable ORDER BY recID"; ""; "" )

should work.

 

 

 

 

 

 

mr_vodka,

Yes ... "Distinct" creates only "ONE" unique value and "Order By" puts the result in the correct order.

Thank you.