You seem to be asking for two things, the ability to clear the field with a single click of the mouse:
Set Field [YourTable::YourField ; "" ]
And setting up a value list that includes the * find operator as a value in the value list.
There are two ways that I can think of for setting up a value list based on fields in a table to include that * character:
add a record where the account ID field has an * as the value. This may require that the field be a text field instead of a number field.
Define a calculation field with text as the result type:
List ( "*" ; Account_ID )
Set up your value list to refer to this calculation field instead of account_ID and you'll have an added * value appear. (But field 2 won't show the correct value)
You may instead want to add a button for inserting the * instead of putting it in the value list.
And you might also be interested in a set up where the fields are global fields that you enter/select criteria while in Browse mode instead of entering find mode. See the scripted find examples found here: Scripted Find Examples
Yes I did think of adding a new record to the season, account and workspace tables but these tables are shadow tables from an MySQL database and we can't do that.
So I thought I might create a local table called say VirtualSeasons, add the first record to it with a seasonID of * then copy all the other seasonID's over to it from the shadow Season table.
But to be honest I don't know how to write a script that will do that.
Import Records can copy the datea form the shadow table into your VirtualSeasons table. But note that you don't have to use a drop down list to add the wild card operator.
Import Records? Isn't that for importing external data?
I already have the data in the Seasons Table and want to copy all records to the VirtualSeasons table. They are in the same .fp12 file
Import records can import records from any table to any other table, whether the target table is in the same file as the source table or a different file. When you go to set up the import and get the open file dialog, you go ahead and select the file that you already have open.
OK, but I'm not sure that will work in the longer term. When I give this file to a customer, I have no idea where it will live. Therefore the path I've set in the script will not be valid.
Grt(Filepath). Sorry I should have looked
You shouldn't need any file path as you can set up a relative file refernce back to your current file. A relative path won't change when you move the file or even if you rename it.
A relative path will have this format:
or in older versions:
This tells filemaker to "refer to the file of this name located in the same folder as the current database file." Since the two tables are in the same file, this will always be the case.
Thanks for your help. I have it working quite nicely now. As the user selects a season, workspace or account the records displayed in the poral adjust accordingly and if they select * it will show all records - in this case "jobs"
(A job can have any combination of seasonid, workspaceid or accountid or none at all
A related question perhaps.....
On the same form I have a field that is another edit box with a drop down value list liked to the master jobs table. The drop down shows all the records in the job table.
It would be great if I could have conditional value lists. So if the user selects a particular season in Field 1, the drop down list in Field 2 (jobs) would only show jobs from that season. Likewise for any combination of season, workspace and account.
Field 1 = Season
Field 2 = Account
Field 3 = Workspace
Field 4 = Job - and is a value list of the jobs table.
It would be great if the drop down list in Field 4 was dependant on the values in Fields 1, 2 and 3.
Conditional Value lists are something that I know a bit about. Here are some links on the subject:
There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.
The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
Thank you. I will jump straight to the SQL method because I'm quite comfortable with SQL but have never really understood where, when or how to use the ExecuteSQL function.
(Currently I just use it in refresh scripts to call procedures to refresh the data in the external MySQL database,)
OK, I'm getting my head around this...I think.
In my case I want the value list to have job names (from the Jobs table) based on the season, workspace and account entered into the INPUT table. My calculation field is going to be (I think...)
ExecuteSQL( " select name from Jobs where seasonID = ? and accountID = ? and workspaceID = ? ; ""; ""; Input::seasonID ; Input::accountID; Input::workspaceID)
Does that look right?
My question is how will it handle empty values or when the user doesn't select a workspace
For example many jobs will not have a workspaceID. So in that case will it return jobs from that match the requested season AND account?
And if the user doesn't care about accounts - in other words wants to see every job from a particular season and workspace regardless of account will this query still work?
Further to my last the following works but only if there is a value entered for all three. If the user only selects a season but leaves the other two blank it returns nothing.ExecuteSQL("select name from JobswhereseasonID = ?andaccountID = ?andworkspaceID = ?";""; "" ; gSeasonID ; gAccountID; gWorkspaceID )
Your SQL is doing exactly what you designed it to do. If the user doesn't select a value for one of the referenced fields, the WHERE clause does not evaluate as TRUE for any record in your Jobs table. Your expression makes the selection of ID's in all three fields required input.
You'd need a different calculation if you want this to work in cases where the user is leaving one or more of these fields blank.