Anyway around field validation preventing the use of Find Operators

[ehwalker]

Hi, I'd like to search in a field with validation set to 'numeric only' using the available find operators (e.g. match empty "=" or, zero or more characters "*") but the find is being prevented by the validation. Is there anyway of getting around this without removing field validation? I realise you can set it to allow overide, but would like to avoid that as well. Is this a recent change to FMP? I'm not sure I've run into this issue before in the past. Thanks.

[comment]

31 minutes ago, ehwalker said:

the find is being prevented by the validation.

I am not able to reproduce this behavior. Can you provide a step-by-step description of what you are doing?

EDIT:
I managed to get the error when trying to use the * or # operators (but not the = operator). To get around this, use a numeric expression - e.g. search for > 99 to find entries with 3 or more digits.

 

Edited March 24, 2020 by comment

[ehwalker]

Hi Comment, thanks for the quick reply. 

Sure.

I have a numeric field "Exam#" that has validation set as follows:

 

Then, within a table view layout - I'd like use the available find operators for the find (e.g. * or # )

When trying to run the find, I get the error that the find value fails the validation and says a numeric value only can be used. If I check "allow user to override..." in the field options, I can override the validation fail and the find works as expected.

 

Thanks for your assistance, please let me know if further clarification is necessary. Perhaps I've made some simple mistake somewhere, but I don't think I've experienced this issue in the past...

Edited March 24, 2020 by ehwalker

[comment]

I believe you have missed the edit to my previous post.

 

[ehwalker]

Yes, okay thanks. I first ran into this issue when trying to use the "*" operator and the omit option to find records that did not have a related child record by using this operator within the related primarykey field (UUIDnumber) from the child table. I guess then there would be no way to do this then without changing the validation options?

[comment]

Why not search the foreign key field?

[ehwalker]

Ah... well, I'm working on importing very messy data from excel and to do this had setup the relationship using a "name" field in both the main database table ("Farms) and a temp. table imported from excel. In this temp table, I wanted to filter out all the records that had parent "farm" records already in the main database (based on these matching names) so that I could see which records within the excel table did not have related "farm" records so that I could create them based of data in the excel table. 

Does this make sense?

[comment]

I am not sure I follow this fully. IIUC, you want  to find records that do not have related records in another table. And you are searching the primary key of the other table - a field that is validated as numeric only. I asked why not search the foreign key field instead - i.e. the match field used for the relationship. I always use this field when searching for related records, because while a primary key field could by some accident be empty, a match field of a related record can never be empty (otherwise the record wouldn't be related).