Importing data into repeating fields in FM 5.x

[Kathryn713]

I have a single text file coming in from a web site to process. The file contains basic information for a single record in Filemaker, plus a listing of items to be brought into a repeating field in the newly created record record. The values for the repeating field appear as separate lines of data in the original text file. Does anyone know of any way to import those lines of data as values in a repeating field in a single record?

 

[marty1452]

To extract a repeating field, you need to write a calculation something like this:

GetRepetition ( repeating field , 1 )&
GetRepetition ( repeating field , 2 )&
GetRepetition ( repeating field , 3 )&
GetRepetition ( repeating field , 4 )&
GetRepetition ( repeating field , 5 )

or for space between the text values so this:

GetRepetition ( repeating field , 1 )&" "&
GetRepetition ( repeating field , 2 )&" "&
GetRepetition ( repeating field , 3 )&" "&
GetRepetition ( repeating field , 4 )&" "&
GetRepetition ( repeating field , 5 )

The second parameter (or the nunmber 1, 2 and so on) is the repetition number to get. The & symbol adds these together. The " " puts a space in between the text. A ", " would insert a comma and then a space.

I hope this helps.

Marty

 

[Kathryn713]

Marty -

Thanks for the suggestion, but that's the opposite of what I want to do. I need to get data from a pure text file INTO repeating fields, not extract data from repeating fields out of FileMaker. Each line of text from the text file needs to be brought into a repetition of the repeating field in a single FileMaker record.

Any ideas out there?

Kathryn

 

[itsisnofun]

Kathryn

Does the incoming text file have a line for each record?

Line 1 would be record 1 and line two would be record 2 so forth and so on.

Also is the data per line fixed length or separted by tabs or spaces?

Give me some details and I will see if I can help.

 

[Kathryn713]

Thanks for your response. This is long - apologies in advance. Holler if I'm not making sense.

Our existing system has all fields (both repeating & non-repeating) in a single file (QUOTES). Data is entered by hand. Each record receives a serial number (= Quote Number). Our procedures require that each quote number be carefully tracked. Gaps in sequence are not permitted, so we use the FM s/n function to assure accuracy.

Now we also have the same information being entered by the customer online, arriving as a single tab-separated text file, one file per request. Each tab-separated text file contains data for a single record, with multiple lines of data. Line 1 of the text file contains data to be imported into the non-repeating fields (name, address, email, etc).

Subsequent lines in the text file contain data to be imported into three repeating fields in the record.
(Desc, Qty, & Size). Each line of data contains info for one repetition of each of the 3 repeating fields.

My current solution is to bring the data into 2 files. First I import the text file into primary file (CUSTOMER) as new records with FM's s/n function giving each a record ID no. I can't figure out how to import just the first line of data from the text file, so the import creates multiple records when I only want one. The first record contains the unique information (Name, Address, etc) , the others contain data that ends up in the repeating fields (Desc, Qyt, & Size).

All but the first record - which contains the data for the non-repeating fields - are deleted. (This leaves desired info in a single record, but results in gaps in the record ID nos when additional files are imported.) I then copy the ID number for that record to the clipboard.

Next the same data is brought into a secondary file (ITEMS) as separate records. The ID number from the primary file (CUSTOMER) record is copied into each of these records. This time the first record in the new set is deleted, as it contains data intended for non-repeating fields.

We then view the information from the primary file (CUSTOMER)with a portal that links to records in the secondary file (ITEMS) using the record ID number. This mimics the repeating fields, but I still have the problem of getting the information into the original 'real' database (QUOTES) without manual cutting & pasting.

Ideally I'd like to bring in the text file into the original file (QUOTES), but I'd settle for a way to merge related records from CUSTOMER & ITEMS into a single record in QUOTES. This involves bringing data from non-repeating fields in multiple records into repeating fields in a single record.

Or if you can help me figure out a way to serialize the record IDs in my primary file CUSTOMER without gaps I could make that work too. The key is that the final ID number (quote numbers) MUST be sequential, unique, & have no gaps. One note - I can't trust those entering this information to check the ID numbers themselves. It must be automatic. And records will continue to be created by hand as well as from text files received from our web site.

Have I given you enough to thoroughly confuse you? Any ideas would be gratefully received. Maybe I'm too close to this to see a solution.

Thanks for your offer of help!

Kathryn

 

[itsisnofun]

Kathryn


I had to fly to NH so I just got your reply. Give me a day to look this over and I will let you if I can help. We do aLot with fmpro and do a lot of transfering of data in and out. Will let you know.

Mike

 

[itsisnofun]

Kathryn


Let me see if I follow. You have data that comes from two sources. One is the web (data to import) and the other is manual entered. Your final data warehouse is quotes or is this the front end to Customers and Items. I am guessing here but are you using Items to list purchase history in the customers file in a portal. Does the quote file any connections to the other two files?

 

[Kathryn713]

Hi ItIsNoFun (BTW, great ID!)

Sorry it took so long to answer you. I was out of town for while. When I got back fire control took the front seat for a few days.

To answer your last post ...

Yes, data is coming from two sources:
(1) a tab-separated text file from the web
(2) manual input by a user in our office

Final data warehouse is QUOTES, which contains both non-repeating and repeating fields. The Record ID No in QUOTES (field defined as Auto-Enter Serial Number, increment by 1, can't modify, must be unique)) is used as the Quote number, no gaps are allowed.

QUOTES is used to record RFQs in which a single customer requests multiple items at once. The customer's name, address, contact info & the quote number are entered in non-repeating fields on a single record. The product numbers, quantity, & price are entered into repeating field in that same record.

As of now, QUOTES has no connection to CUSTOMERS or ITEMS. I set up those 2 to emulate QUOTES so the data from the web can be brought into FM without the user trying to cut & paste from a text file. ITEMS is viewed through a portal from CUSTOMER. I can easily bring in the information from CUSTOMER into QUOTES - that's all the data destined for non-repeating fields from line 1 of the web text file.

My problem comes in trying to bring the related records from ITEMS into the repeating fields of a single record in QUOTES (Product No, Qty, Price). If I can find a way to do that, great. Otherwise it will still be manual cut & paste for at least the repeating field info until I can redesign the system to get rid of those blasted repeating fields!

With thanks & apologies for the delay,
Kathryn

 

[itsisnofun]

Kathryn

What ver of FMP are you running. I am going to build a front test file and play with bring data in. If it works I can email to you for testing.

Mike

 

[Kathryn713]

Mike -

I'm running 6.0 v2. I'll be interested to hear how you make out. Thanks for taking time to help.

Kathryn

 

[itsisnofun]

Kathryn

Tell me which format your incomings data looks like.

format one

Name<tab>address<tab>city<tab>zip
Qty<tab>description<tab>price<tab> line item 1
Qty<tab>description<tab>price<tab> line item 2
Qty<tab>description<tab>price<tab> line item 3

format two

Name<tab>address<tab>city<tab>zip
Qty line item 1
description
price
qty line item 2
description
price
qty line item 3
description
price

Mike

 

[Kathryn713]

Mike -

I did a cut & paste from a code editing program of a raw text file. Here's what you get:

Name Address City Zip
Qty Description Price Line Item 1
Qty Description Price Line Item 2
Qty Description Price Line Item 3


If viewed in Word or a code editing program, symbols for tabs (>> in HomeSite, tab symbol in Word) are visible between each piece of data on each line, paragraph mark at end of each line.

Viewed in Notepad all you see is empty space between data segments with a box marking the end of each line of data, though in Notepad it's all one long line.

If it would help, I could send you a text file as received from the web.

I'm heading home for the weekend soon, but will try to check in from there. If not, I'll be back Mon am EST.

Kathryn

 

[Schlogg]

I think you will find it easier in the long run to re-design the whole thing without repeating fields. (They are a real pain and are not supported very well.) Instead of a single QUOTES file with two types of records, use the CUSTOMER with child records in an ITEMS file. You've already done most of the work to separate the two data types from the incoming single text file.
As far as I have ever found, there is no simple way of entering data in repeating fields - you have to do it manually.

Cheers,
Paul J.

 

[itsisnofun]

Kathryn

I need to know how many line items are on each record in the file.( number of repeating fields)


Schlogg

You may be right. But nothing ventured is nothing gained. Scripts and some creative thinking are very powerful tools in filemaker pro. We have some solutions with repeating fields but we don't have to manipulate that data so this is also a learning process for me. Nothing like a good puzzle to work on.

 

[Kathryn713]

Mike,

Maximum 65 repetitions on the repeating fields. I'm with you on the puzzle aspect. Even when I can't solve it, I still learn something in the process.


Paul,

Thanks for reading & offering your take on this. I do realize repeating fields are a royal pain - even FileMaker recommends not using them! But unfortunately I'm dealing with a legacy system designed years before my arrival. In this case, if the quote becomes an order the data is sent via scripts to several other files with matching fields. A massive redesign to eliminate repeating fields is in process, but with several hundred files, many related & most involving repeating fields & in constant use, it's tricky. Until that's done, I'm stuck dealing with what I've got.

Kathryn

 

[itsisnofun]

Kathryn

I have two files I want to send you before going any futher. I need for you to just check them and see if this is the right direction.

You can email me your address at mcoker@srisk.com

 

[Kathryn713]

Mike

Email on the way

Kathryn

 

[Schlogg]

Kathryn,
You have my sympathy, if nothing else. I spent many hours once trying to do what you are trying to do - unsuccessfully.
I'm dying to see if 'itsisnofun' has a solution!


Cheers,
Paul J.

 

[itsisnofun]

Paul J.

email me your address and I will send solution I sent Kathryn. This works for her problem may not work for all it is also set for 65 repeating fields or less can be expanded to more. This file runs a script and paste data to a repeating field which can then be exported.

Mike

 

[Kathryn713]

Paul,

'itsisnofun' sent me a sample solution yesterday that looks like it will work. I'm refining the programming now to make sure I can integrate it with my other files. If it works (& I really think it will) he will probably post his solution here. I'm sure I'm not the only person to face this issue.

Ain't programming fun? ;-)

Kathryn