How to handle changing data format?

[Darren Emery]

Hello all -

I have hit a wall on this one.

I manage an inspection/property maintenance/permit database for the city I work in. Monthly, we receive an updated ownership file from the county.

Past practice has been to wipe out the old ownership data, and import the new file. This worked fine untill I began to build in some relationships and added features to the system. Now, if we remove the file from the server, and then bring the new one back online - I seem to lose the relational tie and many of the functions I have built no longer operate correctly.

My thought was to import and update the records using the match field, rather than starting over with new records. The problem I'm encountering has to do with the data we receive from the county - the MAC (MAC is a property ID number assigned byt he county) number used to be formated as follows: 0034567. This month, the data came in as 34567. When I import, I now double the number of records in the file, as 0034567 does not match 34567.

Any suggestions on how to handle this issue?

Thanks in advance for ANY suggestions!

Darren

[comment]

If "0034567" is supposed to match "34567", then change the fields' type to Number (on both sides of the relationship) and you'll be back in business.

[Darren Emery]

Unfortunately - many of the "numbers" have a or b at the end of the string. This prohibits a change of field type to number (I think - correct me if I'm wrong here)

[fseipel]

Unfortunately, Filemaker doesn't adhere to real field typing; so number fields can contain text. Changing an existing field to number, will not even clip text portions. I would recommend clicking on MAC field, then doing a Records|Replace field and using replace with calculated results:

Abs ( YourTableName::MAC )&Filter (YourTableName::MAC;"aAbB" )

where absolute value trims any leading zeros, and filter adds back any a or b. Nesting filter within Upper would be even better, to normalize suffix to upper case.

[comment]

OK, then how about:

GetAsNumber ( text ) & Filter ( text ; "ab" )

[Darren Emery]

Thanks for the good advice.

A follow up question (which will likely show my entry level status on such issues.)

If I set up a filter as mentioned above - will it retroactively process all of my data? Mu guess is that this is the records|replace step you mention, but I am not familiar with this step.

And - will this setup process my new data each month, as I import the new batch of data from the county?

Thanks so much for the help - it is GREATLY appreciated!

Darren

[comment]

You could do Replace Field Contents... (be sure to have a backup), or set up a calculation field and use it as the matchfield for your relationships instead of the original.

I'm a bit hesitant here, because I don't understand your original point:

if we remove the file from the server, and then bring the new one back online - I seem to lose the relational tie

I see no reason for that, assuming the file name and structure are the same.

[Darren Emery]

I'm a bit hesitant here, because I don't understand your original point:

I see no reason for that, assuming the file name and structure are the same.

I thought that was odd as well. When the "new" file was brought back online - the fields I use to show matching address (a portal lookup into the related file) had a "File not found" error message.

I think I'll follow the process as utilized in the past to find out what exactly is happening here...

I'll post what I find out.