There is no “Set” data type in Filemaker; the nearest thing would be use a “value list: from field”, which is guaranteed to have unique values.
You could use a summary field 'Type of' to get the combined values, then either feed the result into a recursive function that eliminates duplicates, or use a dedicated field to define a value list on-the-fly and read its values.
Since you're doing this in a report, you'll probably need GetSummary() to get the group result; and you could also define the VLfield in the same file*, like:
Set Field [ YourTable::VLfield; DateTable::sListOfTestText ]
# [ or: GetSummary ( YourTable::sListOfTestText ; sortField ) for a sub-summary result ]
# [ capture set; in a variable… ]
Set Variable [ $set; Value:ValueListItems ( "" ; "SetValues" ) ]
# [ or in a field, for display within the report ]
Set Field [YourTable::setField ; Value:ValueListItems ( "" ; "SetValues" ) ]
# [ Value list “SetValues”, defined as “Field from: YourTable::VLfield” ]
# [ … and reset VLfield ]
Set Field [ YourTable::VLfield; "" ]
* rather than use, say, a field from a dedicated one-record Utility table
The complicating factor here is that field A contains multiple values. If it contained only one value you could use SQL to do a SELECT DISTINCT.
As it is now, you will have to loop through the records, separate the values in each record and then check which ones already exist in your result list.
FilterValues() will come in handy.
I am sorry I am not very good at scripts yet , I don't understand the method you have described .
Do you have a demo file where it is being used ? It would be great ....
well, first off, you are putting multiple VALUES into one field (comma separated). Perhaps these should be in related table?
I was trying to come up with a demo showing FilterValues(). Binu, this function uses a "list" that is return-delimited, so your comma-separated values would first have to be converted to the list:
Substitute ( Field A ; "," ; Char(13) )
From there a "summary" of the lists might be filtered. Wim, what did you have in mind?
Sorry My mistake , there are no commas separating the values .
Just space between each value .
Will that be easier ?
OK, here's what I did:
1. convert the comma-separated to return-delimited:
Field_A_list = Substitute ( Field_A ; "," ; Char(13) )
2. create a summary field (so that you can use a FOUND SET) that pulls together the lists just created:
Summarized_lists = a summary field pointing to "Field_A_list" and selecting "List of"
this will pull together all possibles in a found set.
3. Create a calculation with the FilterValues():
Filtered_found_list = FilterValues ( "1¶2¶3¶4¶5¶6¶7¶8¶9¶10" ; Summarized_lists )
4. This yields your desired result as a return-delimited list. Convert back to comma-separated, if so desired:
Substitute ( Filtered_found_list = FilterValues ( "1¶2¶3¶4¶5¶6¶7¶8¶9¶10" ; Summarized_lists ) ; Char(13) ; "," )
no, to use FilterValues(), they must be return-delimited (see my last reply)
thanks I will try it out
Wow this is working well I think I can use this in my case ..
I forgot to mention that you need FM ≥13, but you seem to have that covered.
OTOH, as was asked before: why do you have lists of values in one field?
Thanks a lot erolst This is in connection to the other thread you had replied earlier regarding a book translation tracking system . You mentioned then some flaws in my data model. However everything was working well so I didnt change it .
2 translators may work on one book but different set of chapters . The chapters are being selected in a checkbox. I want to combine their work in subsummary report :
eg.for BOOK 1
Translator 1 does chapter 1,2,3, 5
Translator 2 does chapter 4,6,7,8,9,10
So in my subsummary part for Book1 I want the full list (1-10)
Hope that makes sense.