Finding with decimal points

[dhsa21]

I have a invoicing database whereas all invoices are referenced by the "case no" field.  Unfortunately when I designed the database eons ago I included using the "case-no" field as a numerical field styled as "12-XXXX".  Each investigator has their own prefix.  The problem is when I do a find (for example 12-1111) it also brings up 12-1111.11 and 12-1111.22.  We use the suffixes for additional invoices on the same matter.  I need to find a way for the user to enter the case number and only that case number comes up, not the case numbers with the suffixes.  Thanks for any and all help.

[LaRetta]

Here is one way:

 

If manual find, just begin the find with == before you type the case number.

 

If script, you could use:

Set Error Capture [ On ]
Enter Find Mode [ Pause ]
Modify Last Find
Set Field [ table::CaseNo; "==" & table::CaseNo ]
Perform Find [ ]
If [ not Get ( FoundCount ) ]
Show Custom Dialog [ "No records found.", Default Button: "OK", Commit: “No” ]
End If

In truth, it should not be a number result but this should work regardless.

 

And welcome to FMForums!!  

[comment]

In truth, it should not be a number result

 

 

Echo that. "12-1111.11" is not a number, and the period here is not a decimal point. And ideally, the suffixes should be in a separate field.

[dhsa21]

Thanks for the replies!!!

[dhsa21]

The replies were GREAT!  But, I need to script it so that when I have a set of X found records the script will take the first "case no" record and find only it (not the "case No" with the decimal point) and then print the invoice from only that one "case no",

for instance:

In the found set:

Find the first "case no" record then perform a subscript to invoice that case no

Find the next "case no" record and perform a subscript to invoice that case no

etc 

The problem i'm having is it combines the case 12-1111 with case 12-1111.11 into one found set which the invoice is produced from whereas it should be 2 separate invoices.  

[dhsa21]

Anyone??

[comment]

I thought this question was answered. I don't quite understand your follow-up question, esp. the part about "first".

[LaRetta]

Did my script not work to produce the found set of the primary case numbers?  

 

The problem i'm having is it combines the case 12-1111 with case 12-1111.11 into one found set which the invoice is produced from whereas it should be 2 separate invoices.  

 

With that found set of records in your LineItems (invoice lines) table, and using a LineItems sTotalAmount summary field, your invoice is printed there.  Or is it?  We lack information as to your structure so it will help if you fill us in a bit.  

 

Oh, I see Comment just responded.  I thought 'first' meant primary which was the 'number' to be isolated in the find.  

 

Hi Michael!  

[comment]

Uhm ... just on a hunch: suppose you have a found set with the following values in the CaseNumber field:

10-123
10-123.1
10-123.2
10-123.3
10-124
10-124.1
10-124.2

you could do this (shown here as a script, but can also be performed manually);

Enter Find Mode [  ]
Set Field [ Table::CaseNumber; "*.*" ]
Omit Record
Constrain Found Set [  ]

to reduce the found set to:

10-123
10-124

[LaRetta]

Oh yes indeed!