Page 1 of 1

Wild card "*" searching a numeric string

PostPosted: Sun May 20, 2018 11:13 am
by SamT
Hi All,

Since the inception in Dataease of our database we have always used a number string for our part numbers
"01/1066/011" is the format using slashes to break up the part number .The prefix, "middle bit" and suffix mean something to us . Now we use the wild card search asterix when searching text fields to search records but have never been able to search our part number because its a numeric string field.

Could anyone please confirm that numeric strings cannot be searched using wild cards or is it something I have done wrong ?

Many thanks

Sam

Re: Wild card "*" searching a numeric string

PostPosted: Sun May 20, 2018 5:27 pm
by KjetilUrne
I tested a little bit on a numeric string with the following format
NumStr.jpg
NumStr.jpg (9.92 KiB) Viewed 610 times

and couldn't see it's much different from searching within other formats.

The only thing I found special was that even if you include other characters than digits in your search criteria, they will not be considered. E.g a search for *01/1* would be valid for both part number "01/1066/011" and "02/1066/011" (because it's actually the same as searching for *011* )

If you only accept "01/1066/011" to be found, you could use the following criteria: 011??????

Re: Wild card "*" searching a numeric string

PostPosted: Mon May 21, 2018 2:51 pm
by SamT
Hi Kjetil,

Thanks for your thoughts.

my numeric string field is 00/0000/000 .Now if I want to search for all items with the prefix 13 (The "13" identifies the part is a subcontract paint service) and I place the 13 in my numeric string 13/* I would expect the search to bring back

13/0000/001,
13/0000/002,
13/0000/003 etc

and finish at 13/9999/999 .

But when i enter the 13 and * wild card in the numeric string field and press search the 13 moves to : 00/0000/013 and finds me the part with the part number 00/0000/013

Any further thoughts as to what I am doing wrong ?

Thanks

Sam

Re: Wild card "*" searching a numeric string

PostPosted: Mon May 21, 2018 8:28 pm
by KjetilUrne
Hmmm... strange...

SamT wrote:the 13 moves to : 00/0000/013

Do you actually see that the '13' gets prefixed with zeros? What version of Ff?


What happens if you search for 13??????? (7 question marks)?

Re: Wild card "*" searching a numeric string

PostPosted: Tue May 22, 2018 1:33 pm
by SamT
yes if I enter 13/* or 13/????/??? or 13/****/***

it reverts to 00/0000/013

Version 1.63

Are you saying that if you create a numberic string 00/0000/000 you can search it ok ?

Re: Wild card "*" searching a numeric string

PostPosted: Tue May 22, 2018 2:46 pm
by KjetilUrne
SamT wrote:yes if I enter 13/* or 13/????/??? or 13/****/***

it reverts to 00/0000/013

Version 1.63

Are you saying that if you create a numberic string 00/0000/000 you can search it ok ?



Yes, it works without problems for me (I have tested this on 1.63).

Just to clarify: I am aware that it has nothing to do with your initial challenge, but the slash in your search criteria will be good for nothing. Take a look at this:
Snap228.jpg
Snap228.jpg (102.65 KiB) Viewed 545 times


(assuming you would have liked to see the "green records" only)

Re: Wild card "*" searching a numeric string

PostPosted: Tue May 22, 2018 2:49 pm
by KjetilUrne
Regardless if I search for 130* or 13/0* the result is the same:

Snap230.jpg
Snap230.jpg (27.16 KiB) Viewed 545 times

Re: Wild card "*" searching a numeric string

PostPosted: Tue May 22, 2018 2:54 pm
by KjetilUrne
Why don't you make an entirely new form (no recycling) with one NumStr field and load it with data to see if you're stuck with the problem?

I wouldn't be surprised if it had to to with its inheritance:
SamT wrote:Since the inception in Dataease...

Re: Wild card "*" searching a numeric string

PostPosted: Sun May 27, 2018 11:36 am
by SamT
Hi Kjetil,

Again my thanks for the time you have spent with my issue.It is appreciated.

I have created a new form with a numeric string field and then loaded with data . I have concluded I cannot search on it.

It would be interesting to create a new copy of Ffenics and a new database so there are no inheritance issues but I am unable to get onto the FFenics shop to get into my account to re download one of my licences (I will have of course deleted the same licence before I carry out this step to reinstall) .

Thanks again

Sam

Re: Wild card "*" searching a numeric string

PostPosted: Sun May 27, 2018 12:18 pm
by SamT
just tried creating a new database from an existing copy with a new table with a new numeric string field and a text field 11 characters long that derives its value from the numeric string files i still l cannot search using the numeric string field but can search using the text field... interestingly the text file inherits not only the numbers but also the slashes


Then thought ...drop down menu.. Applications-preferences-database administration - unclicked "zero-pad numeric strings" but this just stops the part of the numeric field after the * from filling at all and search still does not work

Re: Wild card "*" searching a numeric string

PostPosted: Mon Jun 04, 2018 2:15 pm
by Gil Fleming
Sam, there are a couple of ways to do this, depending on whether or not you just want an answer or to locate records for viewing.

Assuming you want to view/edit the records, you could create three new text fields from your PartNumber Field. Let's call them Part1, Part2 and Part3, derived as follows:

Part1 derivation: Firstc(Partnumber,2)
Part2 derivation: Midc(Partnumber,4,4)
Part3 derivation: Lastc(Partnumber,3)

If you initially define the fields as virtual, save, then remove the virtual property, your form will be fully populated with the new text fields. Then just search on the relevant text field.

If you want to just display some blurt about the searched part numbers (assuming that you are conducting a fairly routine enquiry), you could have a procedure that prompted you for a choice of search part (part 1,2 or 3), then used the text handling functions above to locate matching records. We call it STS (Standard Textbook Stuff).

Goes without saying that you should backup your data before doing any of this. :mrgreen: