Import info from a file and then perform a find

[sofie]

Hi

I want to import data from a text file (in this case it will be a telephone number). I then want to perform a find so that if there is a matching customer record containing the telephone number then the record will be displayed.

Could you please help!

[MoonShadow]

Is this a one-time thing or ongoing? You haven't described your structure. What other data will be coming in? What is the purpose of this find? Generally, I would suggest an intermediate table otherwise you will end up with duplicate phone numbers if importing directly into your Customers table. You could create another file (or table) called Import.

In both Import table and your customer table, create a calculation called cFilterPhone (text) with:

Filter(phoneField; "0123456789")

Create a relationship from Import::cFilterPhone to Customers::cFilterPhone on =

From an Import layout, perform your import of the phone records. Place your Customer fields you wish to view directly on the layout (or if there is the possibility that there are more than one, place your Customer records in a portal). If they match on phone number, data will appear. But without knowing more what you're trying to accomplish, it's all speculation ...

[aaa]

Let your file has next fields:

Name

Telephone

Create one global field Global_Tel_Num

Put it on header of layout. Then create script near this:

Import records(without dialog)(You must once importing records manualy)

Enter filnd mode

Set field[Telephone,Global_Tel_Num]

Perform find

[sofie]

I want to be able to run a script that will allow me to read a text file. the text file contains a telephone number. when the telephone number is read from the file it should trigger a search to find the matching customer record and display it.

At present i am importing the file and saving the telephone number in a field called CLI. I just want to be able to match the phone number field to the CLI and if there is a match the customer record should be displayed.

Its the same principle as a call center a call comes in and if the number is recognised it should display records related to the customer with the matching telephone number.

I'm not sure if you understand what i'm trying to do now. If not let me know.

[sofie]

I have managed to create a global field called CLI.

How do i put this on the header of the layout?

[MoonShadow]

Have you had any problems with number format inconsistency? They will need to match exactly with dashes etc. And is there always only one number? If numbers are consistent and only one number, aaa's method will work for you using a global.

But you may wish to control it a bit more. Do NOT select 'Add New Records' and, since globals apply to all records, there is no need to update a set. Instead, import into only one record. Something like:

Set Error Capture [On]

Allow User Abort [ Off ]

Show All Records

Omit Record

Show Omitted Only

Import [ No Dialog ]

Enter Find Mode [ ]

Set Field [ customerNumber; global ]

Perform Find [ ]

# BTW, with vs. 7 you do not need to manually perform your import first. Imports are stored within the script. This import should relate the phone number to the global only and click 'Update Existing Records In Found Set.'

You can be on any layout you wish. It doesn't need to be in the header but if you're on columnar list view you may wish to add a header part (Layouts > Part Setup).

[sofie]

My task is to automate the searching of records within filemaker. The text file which i need to import will only contain a single telephone number. I believe i can import this single telephone number into a global field i can then use this global field to perform a search on phone fields phone 1 and phone 2.

Could somebody please guide me on how to do this?

I have already created a global text field called CLI.

[MoonShadow]

Wow. You're an impatient sort ... particularly at 2:30 AM in my part of the world.

[MoonShadow]

Since there are two customer phone numbers to search, modify the script thus:

Set Error Capture [On]

Allow User Abort [ Off ]

Show All Records

Omit Record

Show Omitted Only

Import [ No Dialog ]

Enter Find Mode [ ]

Set Field [ customerNumber1; global ]

New Record/Request [ ]

Set Field [ customerNumber2; global ]

Perform Find [ ]

[sofie]

Post deleted by sofie

[MoonShadow]

Regardless of the time there, Sofie, 11 minutes is not long enough to give someone a chance to seriously consider what you may need much less type a response.

Let us know if you get stuck on it.

[sofie]

I'm stuck -(

So...the text file only contains a single line of text. This text is a telephone number.

The first step...I'm trying to import this into a global text field called CLI.

This doesn't seem to be working correctly. It comes back with an error message saying that the number of records in the database and import file do not match, and some of the records have not been updated.

I thought importing into just the global field would effect all records???

[MoonShadow]

1) Import Action - do NOT add new records.

2) At the bottom do not click Add Remaining Records.

3) You should match your arrow from the field on the left (your phone number) to the global field on the right.

4) Did you isolate only one record in your found set in Customers table first?

5) Your text file may not be 'clean', ie, it may have carriage returns or hidden characters which makes it appear like more than one record. Can you attach it?

6) Did you create the script first and store the import within the script?

7) Did you duplicate my script exactly?

I just created a test and it works perfectly if all factors mentioned are adhered to and your data is clean.

Update: You are correct a global affects all records. But you only need one record for this process to work. In case you have a carriage return in your text file, you might try removing the Omit Record/Show Omitted portion; which should then allow the import. I added it because I think it would be quicker if you have 100,000 records (for instance).

[sofie]

OK, that worked :-)

Now...when performing a find I want to do the following:

if phone 1 = global or phone 2 = global then add / display the record in the found set.

?

[MoonShadow]

My script will do that ... starting at Enter Find Mode [ ]. Create the whole script.

It enters the global contents into Phone 1. Then creates a second Find request and enters the global contents into Phone 2. It will find the Customer that has a matching phone number in either field. What you wanted? If not, speak up ... it's quite late/early here and I'm on an all-night project ... I'm a bit rummy so may not be tracking with you exactly.

[sofie]

It works!

Unfortunately it's an exact text match search. Not really what I wanted but it will have to do.

Thank you!

[MoonShadow]

Not really what I wanted but it will have to do.

Not so. You have vs. 7 and can strip or match on portions of the number if you wish.

In customers, create a calculation (text) called cFilterNumber (do not click 'Do Not Store'). But, because you're searching for two numbers and you need a calculation anyway combine them as follows:

Filter(Phone1; "0123456789") & " " & Filter(Phone2; "0123456789")

Adjust your Set Field script-steps to search this calculation instead. Now all you have to do is strip that text in global like this:

Set Field [ cFilterNumber; Filter(global; "0123456789")

Remove the New Record Request and second Set Field []. You need only one find request to search both fields.

You can also search on partial phone number matches. I do this all the time; for instance, search for only last four digits of the phone number or drop off the area code.

[-Queue-]

You could also use the filtered calc

Filter( Phone1; "0123456789" ) &