Instead of a value list or a portal, I'd consider a calculation field with ExecuteSQL (if using fileMaker 12) or a list view layout using sub summary layout parts and no body layout part to list one instance of each committed date.
I like the idea of the SQL statement (although I'm a bit rusty). However, how would that calculation field be translated into a value list that can be selected from a drop down so I can pass the date as a variable?
What you can do with the calculation field is make it multiple rows of text tall and use the onObjectEnter script trigger to perform a script that uses the cursor location--which is determined by your mouse click, to determine which row of text in the calculation field was clicked. This does not work if you have to give the field a scroll bar.
On the other hand, a return separated list of values produced by ExecuteSQL could be defined in a table of just one record and then your value list can list the value from that one record in that one field.
The simplest method is to use a list view layout of sub summary layout parts to condense your list down to just unique values. A button in the sub summary part can then be clicked to select the data for that row of data.
Hmm... unfortuantely, since I'm doing this as an IWP, I can't use script triggers.
Nor can you use a summary report with sub summary layout parts.
That does leave the 2nd option, however.
And it's not impossible to use a portal for this.
Please, always tell the forum when you are needing this to work with IWP. There are many limits imposed by IWP that people who are trying to help you need to know so as not to waste their time suggesting solutions not compatible with IWP.
It was noted in the original. If I use the ExecuteSQL option and have a table with just one record, will the Value List display the entries as multiple values that can be selected?
I also just stumbled upon this possible solution that I'm trying to wrap my head around...but I'm not sure it'll work since I have parent - child - grandchild and the relationship I need is to show unique dates from the grandchild for the selected parent.
This worked...although I'm not sure I understand how sicne I don't understand the statement. But, I replaced the fields in the filter statement with the fields in our tables and it worked.
Now, to research it more to understand what is happening.
Apologies for missing that statement in your original post.
Not a problem. I realized it was buried in the post. In the future, I'll note it at the top of the post. If you missed it, others can easily miss it as well and I don't want to make the people who are helping me mad!
Now, I have it partially working. It will display a list of unique dates, which is great. But, I only want to display dates that aren't committed (KPI Table, committed=0). However, I don't know how to modify this filter to include that. If you have any suggestions, I would appreciate it. Of note - there is one extra field in the KPI table I didn't mention (didn't seem relevant). But, there is a KPI_ID field which is just a serialized number.
Let ( [
indexValues = List ( KPI::KPI_ID ) ;
searchValues = List ( KPI::Date_of_Shift ) ;
index = ValueCount ( Left ( indexValues ; Position ( ¶ & indexValues & ¶ ; ¶ &KPI::KPI_ID & ¶ ; 1 ; 1 ) ) ) ;
prevValues = LeftValues ( searchValues ; index -1 )
IsEmpty ( FilterValues ( KPI::Date_of_Shift ; prevValues ) )
Using the example that you have posted, you would need to use a relationship that only matched to KPI records where committed = 0
you might use a field constZero with the number 0 as it's sole term as that additional match field in the relationship.
Hi. I'm an amateur user of Filemaker (only 15 years or so....) - so, I'm sure you guru's can improve upon the answer I _found_ somewhere else on the net. I have a fairly large list of women's names. Imagine how many "Sue" and "Susan's" and "Deb" and "Debbie" and Debi's there were. Hundreds of records with the first name of "Sue", etc.
I wanted to "find unique FIRST name records ONLY".
The trick is to create a SUB-SUMMARY field - and tell FM to create that Sub-Summary when sorted by first name.... THEN choose the export command AND during that export dialog, tell it to GROUP by First Name. Voila! ONLY those unique first names will be exported.