Exporting Repeating Fields

[Courtney]

I know Repeating Fields are kind of a nightmare, and it's something I probably need to address. This was a very old system originally begun in FM 4.0 or so, and it's just not something I've fiddled with very much yet, but now I have an issue.

 

I am using Repeating Fields for expenses billed to clients on a time-based invoice. In other words, we may bill them for 15 hours of professional work, and then some additional expenses, such as long distance telephone charges or office supplies used in assembling their final product. We also use repeating fields to bill for pass-thru expenses, such as hiring an outside contractor to do some specific research or writing for the project.  So I have four repeating fields total, two with descriptions of the expense or pass-thru and two with the amounts being billed.

 

Every month, after invoices are complete, we run a script that exports the invoice data to a tab delimited file, and then it's run through some macros in Word and Excel to prepare it to be uploaded as invoice data into Quickbooks.

 

Until this month, the first step of this process was to open our exported data in Notepad, and then the Unicode "boxes" were visible to represent each repeating field that was empty of data. I then replace all the boxes with asterisks, and my Word and Excel macros handle the asterisks through all other steps until uploading to QuickBooks. (I keep the empty fields represented by asterisks, so that my Word and Excel macros can calculate mathematically how many lines of data are between the start of each new invoice. I wrote these processes 8 years ago, so I imagine they could use some updating, but management is understandably very wary of change where it involves our monthly revenue processes.)

 

Now that most users have been upgraded to Windows 7, the Unicode boxes are no longer visible, and there is nothing to "find and replace".

 

So, two questions, I suppose:

 

1 - Is there any way to make those empty repeating fields show up as some character that can be worked with in Excel, Word, or Notepad?

 

2 - Any suggestions for some reading on a solid way to get rid of repeating fields? I feel like this is going to be a major project, but I've put it off long enough.

 

~Courtney

[comment]

When you export repeating fields to a tab-delimited file, individual repetitions are separated by the group separator character (ASCII #29). Whether this character is "visible" in the target application or not should not matter at all - it is there.

[bruceR]

Attached is a very basic example that steps through the process of adding an items table, splitting the original repeats into separate records.

 

In step 1, you copy the repeating fields; plus the invoice ID field; from the original invoice table.

In step 2, you define a new table, invoice items, paste the fields into place, and set number of repeats to 1, and add a new record ID field.

In step 3, you modify the relationship diagram

In step 4, you modify the invoice table

In step 5, you import the data, splitting repeats.

 

File TimeBillingReps1.fmp12 is the original unmodified file.

File TimeBillingReps2.fmp12 shows the results after the new table is added.

File TimeBillingReps3.fmp12 shows the final result for this very simplified example.

 

See if you can take TimeBillingReps1.fmp12, make a copy and get to the end result by yourself.

 

Of course when working with your real files, make sure you have backups!

TimeReps.zip

[Courtney]

When you export repeating fields to a tab-delimited file, individual repetitions are separated by the group separator character (ASCII #29). Whether this character is "visible" in the target application or not should not matter at all - it is there.

 

Thank you - this actually helps solve my immediate problem. I did not know the ASCII character that I was looking for.

Attached is a very basic example that steps through the process of adding an items table, splitting the original repeats into separate records.

 

In step 1, you copy the repeating fields; plus the invoice ID field; from the original invoice table.

In step 2, you define a new table, invoice items, paste the fields into place, and set number of repeats to 1, and add a new record ID field.

In step 3, you modify the relationship diagram

In step 4, you modify the invoice table

In step 5, you import the data, splitting repeats.

 

File TimeBillingReps1.fmp12 is the original unmodified file.

File TimeBillingReps2.fmp12 shows the results after the new table is added.

File TimeBillingReps3.fmp12 shows the final result for this very simplified example.

 

See if you can take TimeBillingReps1.fmp12, make a copy and get to the end result by yourself.

 

Of course when working with your real files, make sure you have backups!

 

Thank you for this also! I'm going to look into this after the first of the year, so that I'm not spending my whole holiday on it!

[bruceR]

Or do it in an afternoon. It's not that hard.