Find record with multi AND criteria

[RDWirr]

I am running FileMaker 14 on a Win 10 machine. I have a set of records that each have a field with a starting number and a field with an ending number. I have to perform a find that locates the record where the find criteria is greater than or equal to the starting number AND less than or equal to the ending number. I need to write a script that will just let the user enter the find criteria in a edit box and then go to the record. This will theoretically be only one record that would match from the dataset. Can anyone point me in the right direction with this?

Thanks

RDW

[comment]

17 minutes ago, RDWirr said:

have to perform a find that locates the record where the find criteria is greater than or equal to the starting number AND less than or equal to the ending number.

What exactly is the "find criteria"? Will it be a single number?

If yes, make the user enter it into a global field, then have your script do:

Enter Find Mode []
Set Field [ YourTable::StartNumber; "≤" & SomeTable::gNumber ]
Set Field [ YourTable::EndNumber; "≥" & SomeTable::gNumber ]
Perform Find []

Add error capturing as required.

 

 

[RDWirr]

Thanks Comment, that works perfectly. To answer your question, yes, the find criteria is a single number. I was fumbling with this at first because of getting strange find results but then realized that my start and end numbers were formatted as text in the table. Once changed to numbers, it works perfectly.

I guess I need to brush up my scripting skills.

[RDWirr]

Building on this last solution, I realize I have another more complicated problem. I am trying to perform a find on records which have a single field that must be parsed into multiple criteria. I am trying to finding a single record that has a field that contains data like this "295621-295640,295642-295660,295662,295670,....". Find criteria would be for example 295645, always a single number. I think I need to step through the data in each record, parse the start and end numbers of the ranges with the hyphen perform a find and then start a new find range with a comma. If there is no hyphen, then it is a non-sequential number that still has to be detected and searched. Some of the records can have 100's of these mixed sequences in them. The current database has around 30,000 records in this table and growing quickly so the time to step through all this to find where a number fits could become problematic but that is a secondary concern. If it takes a minute or two to perform the find, no big problem.

I am unclear if I should be making a new post for this but since we were on the subject, I thought it best to just carry on.

Thanks in advance,

RDW

[Lee Smith]

2 hours ago, RDWirr said:

I am unclear if I should be making a new post for this but since we were on the subject, I thought it best to just carry on.

This is the correct place.

It’s not unusual to have other question about the same need, that will come from the ongoing questions and answers in your quest for a solution to your question.

I’m confused about your numbers, the searches your doing.  The data seems to be single numbers ranges of numbers? But I got the impression that the range of numbers were in one field? Can you give more detail as to what you have now, and the searches you are trying to use for the finds.

Lee

[RDWirr]

Hi Lee,

Thanks for the guidance.

I have a table with some records, in each record is a field that has this mixture of ranges and single, non-sequential numbers. The content in this field varies from record to record, some can be just a single number, some can be a contiguous range i.e. 12345-12350. But often there are a mix of both contiguous ranges and non-sequential single numbers i.e. 12345-12350,12360,12362...etc. The numbers to find can vary in length from a single digit to the millions. I need to find the record which, in that field, my find criteria matches either by a direct match with a single non-sequential number or within the range of the range type numbers, i.e. 12347 is found in the record that has the data in the field I am finding in "12345-12350,12360,12362". I have attached here some examples of the numbers in the field I want to search. To reiterate, I am trying to find the record that my search criteria matched in the wins_used field you see in the attachments. Basically the application is to find which shipment a particular serial number (win) was in. My operation is to put the Find criteria in the FindShipment field, you see in the image, then trigger a script to search through the wins_used field in all the records. 

I am sure there is a way to do this but it is as yet beyond my skill level.

[comment]

2 hours ago, RDWirr said:

in each record is a field that has this mixture of ranges and single, non-sequential numbers.

2 hours ago, RDWirr said:

I need to find the record which, in that field, my find criteria matches either by a direct match with a single non-sequential number or within the range of the range type numbers

That's not possible. Your field contains not numbers, but text - a bunch of meaningless words. By performing a find, you can find records that contain words that begin with a given string (this is the default find) or records that contain the given string.

You will need to restructure your database and put all those values into individual records in a related table. Or perhaps enumerate the ranges as individual numbers.

 

Edited March 25, 2016 by comment

[RDWirr]

Thanks for the input Comment. I was afraid of this. So no way to find anything that looks like a range, convert it to a pair of numbers and then do a calculation? The problem we have is that this information is entered manually by users who are shipping items with these specific serial numbers (wins). I suppose we will have to build a system to give the user a list of possible serial numbers to select but that could get a bit cumbersome, clicking through 100's of numbers. Any ideas on possible ways to manage this would be much appreciated. 

[RDWirr]

I guess the next logical question would be, how to convert this mix of data to a related table? All the records with a single serial number are straight forward but how to parse the records with multiple comma separated numbers, each with the accompanying 'no_shipment' match field? But more importantly, how to list out the ranges? I have searched around and haven't found any techniques that clearly describe how to convert a range (,123-125,) to a list. I have many thousand of records in this table that have mixed data and converting it all manually seems like a daunting task.

[comment]

I think you have two questions here:

1. How to convert what you already have?
2. How to enter data from now on?

The first question is relatively easy to answer - provided your data is entered consistently. If, as per your example, there is a comma separating each number/range, and a hyphen separating the start and the end of a range, you could have your script:

• strip out all spaces (in fact, any characters excepts digits, comma and hyphen);
• convert commas to spaces (i.e. create a word for each number/range);
• loop-create a related record for each word/value;
• substitute the hyphen with a space (i.e. convert a range to two words);
• set the RangeStart field to the first word;
• set the RangeEnd field to the last word.

With regard to the second question, you could train your users to enter the data directly into a portal - or perhaps have them enter the numbers into a custom dialog running in a loop. Or you could let them continue as before, and run the script on every modification of the field (but delete any existing related records first).

 

Edited March 26, 2016 by comment

[RDWirr]

Thanks for the good information Comment.

Yes, there were two questions.

The important one is how to convert what I have now. I think my data is relatively clean in terms of consistently entered. but probably some cleaning is well advised. As you can already notice, I am not so skillful with scripts, but always willing to give it a go. I have a couple questions though:

• I assume this is a multi-stage operation that you suggest. First separate everything that is a single number/word and create records for each of these, deleting them from the original data, before going on to substituting the hyphens for spaces in the case of ranges. Otherwise the single numbers and ranges cannot be discriminated. I will have to muddle through this but my first puzzle is how to determine if the word between the commas/spaces is a single number or a range. They can have variable numbers of digits.
• How to create a list of records from the RangeStart and RangeEnd fields?  In my newbie way of seeing things, it seems like I would have to move each separate range to a field, substitute the hyphen and then perform the creation of the list of records within the range, then loop back to take the next range in the record and repeat.

The second question is something we have to do some human engineering on. The users are customers and spread over a wide geographic range, training could pose a challenge. We actually have the list of all possible serial numbers that we give each customer so asking them to check them off as they use them is good from a data standpoint because it gives us a more robust auditing dataset and probably less prone to entry errors, but getting them to click through a long list of single numbers would for sure cause some complaints. Maybe the best place to look is how accounting systems handle serialized item invoicing and stock movements. Anyway, your suggestion to use a related table with each serial number enumerated is a very good one. Just a matter of how we manage implementation.

[comment]

I am afraid you have misunderstood my suggestion. It does not enumerate the ranges. It creates a (single) related record for each range, with a RangeStart and RangeEnd fields to hold the given range boundaries. In the case of a single number, it will create a related record where RangeStart and RangeEnd will hold the same value.

If you wanted to enumerate the ranges, you would not need to have a related table - you could enumerate them into a field in the same record of the existing table. And If you output the result as a return-separated list, you could use a relationship to "find" a given number, instead of performing a find.

 

 

Edited March 26, 2016 by comment

[RDWirr]

Aha! That broadens my view considerably. I need to play with this a bit to decide which is the better route whether keeping everything in the range structure or enumerating the ranges and storing it all as single numbers. In the mean time, could you give me a hint on how to or where to find the way to enumerate a range with a script? I have found a couple custom functions on line but no scrip examples.

Thanks very much Comment for your help with this.

[comment]

2 hours ago, RDWirr said:

could you give me a hint on how to or where to find the way to enumerate a range with a script?

Suppose you have a variable named $word, holding two numbers separated by a hyphen. After running:

Set Variable [ $range; Value:Substitute ($word; "-"; " " ) ] 
Set Variable [ $rangeStart; Value:GetAsNumber ( LeftWords ( $range ; 1 ) ) ] 
Set Variable [ $rangeEnd; Value:GetAsNumber ( RightWords ( $range ; 1 ) ) ] 
Loop 
  Exit Loop If [ $rangeStart > $rangeEnd ] 
  Set Variable [ $enum; Value:List ( $enum ; $rangeStart ) ] 
  Set Variable [ $rangeStart; Value:$rangeStart + 1 ] 
End Loop 

you will have a variable named $enum holding a list of all the numbers within the range.

[RDWirr]

Super! Many thanks Comment. This is exactly what I need to get rolling with a solution.