I cannot figure out how to add a new record via a “Reference” field drop down (like shown below) when the reference field is filtered by a drop-down condition. In the figure below, I had to remove the drop-down condition in order to be able to add a new record from the drop down context.
What I am trying to do is KEEP my drop-down filter condition, AND be able to add a new record from the drop down using that cool green plus button seen in the image above.
TLDR;
Here is my setup. I have three tables:
- Case Summaries (containing summarized information about each case or garnishment we have)
- Contacts (contact information of various related parties to each case: ex Sheriff, Garnishor, Creditor, Debtor, and Clerk of Court to mail notices to)
- ContactTypes (ex employee, creditor, court – ie clerk of court)
A record on my Case Summaries table has a number of contact fields:
- “RogsTo” is the contact to whom we send interrogatories.
- I have my drop-down filter set so that it only shows contacts from the “Contacts Table” that have a ContactType set to “Court” (ex clerk of court, but I just put “court” for ease).
- “Employee” is the contact who is being garnished.
- I have my drop-down filter set so that it only shows contacts from the “Contacts Table” that that have a ContactType set to “Employee”.
- Other contact fields (of similar nature as the 2 mentioned above)
You may ask why am I using 3 Distinct Tables instead of 2 and a Table Summary?
-
The reason I have three tables instead of only 2 is because I wanted to be able to click a row in my left hand column, and quickly filter all of my records by contact type.
-
If you instead try to use a table summary to generate the list of “ContactTypes”, then the contacts table is not filterable by the summary table:
Therefore, can you please give us the ability to KEEP a drop-down filter condition AND be able to add a new record from the drop down using that cool green plus button seen in the first image posted above.
UPDATE:
I just noticed that it makes no difference whether the field used for the drop-down filter is a “reference” type or a “Choice” type…
Either way, once you filter a Reference drop down, you can no longer add a new record by using the Reference drop down.
New Setup
On my referenced table (“Contacts”) I have set the field “ContType” as a Choice field.
Back on my main “Case Summary” table, I have modified my reference field dropdown condition to filter based on a “Choice” type field (as opposed to what I previously had - a reference type of field):
Still, cannot add a record via the drop down reference field called “Rogs To” (regardless of the fact that the field that I am filtering on is now no-longer a “reference” type of field… it is now a “choice” type of field):
Hi @Matt_Billiodeaux.
You’re correct - the presence of a dropdown condition is what determines whether the option to add an option from the dropdown is present.
When deciding how the feature would work, we decided early on that it would be undesirable in many cases to show the shortcut to add an option. Specifically, there were questions around what to do when offering to add an option that would immediately be filtered by the current condition, or when an option already exists, but did not appear in the dropdown because it was filtered.
We are still open to the idea of keeping both conditions and the add shortcut, but haven’t had time to think through precisely how it would work.
George
Well, if the drop down is filtering by something like this:
choice.ContType == "Employee"
My vote would be for the behavior to perform as in the example below.
Assume you have a record set called “Contacts”.
- Each record has two fields: “PersonName” and “ContactType”.
- ContactType can be either “Employee” or “Creditor”.
- PersonName is free Text
Now assume you have a field in another table… it is a reference field to “Contacts.” Furthermore, it is filtered with the following rule : choice.ContType == "Employee", and finally the display field of the reference is “PersonName”
- Have the reference selector field act kind-of like a combo box so that…
- The value typed into the box reveals and allows the user to select records that match both:
- the typed value to PersonName, and
- the filtering rule
choice.ContType == "Employee"
- ( note this is the current behavior that getgrist already implements, the next part is the suggested change)
- If the value typed does not return any matched records (ex nothing matches
choice.ContType == "Employee" filter with the PersonName that was typed) then …
- Allow the user to create the new record by hitting either enter or the green plus button. At which point, the record will be created as follows:
- PersonName will be set to the value typed in the selector field
- ContType will be set to match the rule, which in this example is “Employee”
- If a record already exists with some fields duplicated (ex another record with the same first name and last, but as a “Contractor” instead of as an “Employee”)… so be it: a new record needs to be created with the typed values entered into the appropriate field and (in this case) the
ContType needs to be “Employee” (to match the condition choice.ContType == "Employee"). Get grist already allows duplicate values in columns; this record is unique because this record is for an “Employee” (as per the choice.ContType == "Employee" filter applied, and not a “Contractor”)
- If the user does NOT want duplicate values in the record set (duplicate being defined as duplicate PersonName, then I would handle that with a notification box (ex “another entry exists by that PersonName” and put that notification in red right below the green plus button)
1 Like
Thanks for the detailed writeup.
That’s a good point about ContType - it sounds reasonable to try to infer the column values of a new record based on active dropdown conditions. Dropdown conditions can be quite elaborate (though usually, they are simple, like the ones in your example), so a generalized solution would also need to account for more complex conditions involving and/or.
It may also need to be an option to allow creating new records (e.g. checkbox), for cases where the underlying data will not change frequently, or to prevent new records from being added by mistake.
George
Ah… Just started using the filter, so I didn’t think about more advanced filters.
What about having (then) a separate condition / rule input box for setting the default value using python?
for example:
The only reason I put a drop down for the user to select the field which will be set is because I didn’t know if you had a way to set a default value entirely within python (something like if (ContType=="Employee") NewRecord.ContType = "Employee")
