4 Replies Latest reply on Dec 6, 2013 10:59 AM by JessicaThompson

# Importing with a partial matching record

### Title

Importing with a partial matching record

### Post

Hello,

In our database, we have a merchant number field that is 12 digits long. I need to import a spreadsheet that has new information tied to each of these merchant numbers. The problem is that in the spreadsheet, the merchant number field only has the first 9 digits of the number.

These are bank-assigned numbers and the 9-digit numbers are unique so there are no doubles. I do not know why the bank provides some reports w/ only 9 digits and some with the full twelve. Anyway, I tried to import but it seems that the fields have to match exactly, and there is not any way to match by the first nine digits only. There are too many records to consider doing it manually.

I'm wondering if 1, there is some way to match partially and import based on that match that I haven't found. If not, I've thought another option could be 2, making a script that would create a second, 9-digit merchant number field in each record based on the existing 12-digit number field so I could import based on an exact match. I have no clue how to create a script that would do this. If that's not possible, I also thought I could, 3, export the entire table into Excel and see if Excel will let me merge the two spreadsheets some how based on a partial match and then reimport the entire thing again.

Any guidance is greatly appreciated!

• ###### 1. Re: Importing with a partial matching record

Define a calculation field with this calculation:

Left ( TwelveDigitField ; 9 )

Match records using this calculation field and you can now match by the 9 digit code.

• ###### 2. Re: Importing with a partial matching record

Hi, I created a new field and made that the calculation. It is not returning anything. So my calculation exactly reads Left ( Merchant Number ; 9 )

"Merchant Number" is the 12-digit field. I looked it up here: http://www.filemaker.com/11help/html/func_ref3.33.64.html and thought maybe it wasn't returning anything because I had made the new field "Number." I switched it to "Text" and it still isn't returning anything.

Any suggestions are greatly appreciated.

• ###### 3. Re: Importing with a partial matching record

"Not returning anything". Does that mean that the field is blank?

This should be a field of type calculation. I think you set up a text field with an auto-enter calculation. If you add a field with an auto-enter calculation, it won't automatically update in existing records--my best guess from what you posted.

You can switch this field to be a Calculation field by changing the field type in Managed | Database | Fields. If there is a reason why you want to keep it as an auto-entered calculation, see this thread: Updating values in auto-enter calc fields without using Replace Field Contents

• ###### 4. Re: Importing with a partial matching record

It works! Thank you so much. I am pretty new to this, and I had been setting up all of my calculations as auto-entered simply because that was the first way I learned how when teaching myself and didn't know there was another option. Thanks again.