Field type and format affecting search capability

Discussion created by Cécile on Sep 11, 2017
Latest reply on Sep 11, 2017 by Oliver_Reid

Two fields: (none is primary key)


FiscalYear - FileNumber


(FY is a drop-down so no issue.)


FileNumber: Serial starting at 00011, increment by 10


If Type is set as number: Searching for 2 won't yield  file 00021, nor 00201, nor 00221

But searching for 21 will yield 00021 and 00221

I suspect there is something about the increment affecting this since in the past, when the increment was set to 1, it would yield anything that contained the digits entered.


If Type is set as text, then it is worst: it only finds the exact string so one would have to type 00021.


Now you must wonder why the 10 increment. After a year of rolling the complex way we manage cases, and the need we have sometimes to split a file in several cases, although that is not the majority of the files that need that feature and the loss of sequence when lead-in communications have been logged and it is only later, in the assessment process that one realizes that the communication needs to be split to address x number of cases identified in that lead-in message. That was quite a run-in sentence!

Anyway, I figured if we all created the files in increments of 10, when we need to split a lead-in, we have 9 numbers to play with. However, to accomodate legacy of other related systems and to reflect the number of lead-in files, not the number of cases, putting a period between the last two digits gives exactly the format required.


So File 0003.1  is file 3 case 1,    File 0004.1 and 0004.2 is File 4 with its related cases 1 and 2.


Now, I tried to put the period in the auto-enter options but Filemaker ignores everything left from the period so it does not add the 10 increment to the whole number but only the number right of the period. So I created another field, TextFileNumber, which is set when the record is created/committed


n=GetAsText(Files::File Number);




That works fine.


The problem is when the user needs to search for records and only want to type the last few digits. Filemaker finds nothing, despite that there are fields with such digits in them. Having to type 00011, when searching for the record filenumber 1 is a major irritant (already got the complaint!)


How should I go about this? Can the find function be emulated/altered by script so that it adds wildcards so that the entire exact number is not required?