Advanced importing of very old .dbf table

[JSS]

I'm converting a very old invoicing application in DOS (written in 1990) that is still used everyday at work, to a Filemaker Pro database. The default invoice starter solution in Filemaker Pro 13 will do perfectly, but importing the 20 years of data is what's bothering me and I really could use some tips & tricks. The old database files are .dbf (dBase) files, which Filemaker pro can import, but the old database has a few different columns.

 

The customers are already imported, but the invoices table is a real problem. The old database uses one table, and Filemaker uses two. An invoices table and an invoices data table. This is a very basic visual representation with left the old table, and right the filemaker tables:

 

 

 

Problem 1:

The TEXT column in the old database (left) contains the invoice data in plain text (on each new line a new product, with the count and unit price). Like this:

 

 

— 2x battery of brand X                      140.00,-

— 1x battery of brand Y                      120.00,- 

 

What I would like to do is parse the text (regex? pattern matching?) and insert it as the invoice data in filemaker. Or is there a better way to handle this?

If this is too hard in Filemaker, maybe I can convert the dbf files to a more usable format and write a script to recreate the table in a more usable way. The total & VAT will be automatically calculated by Filemaker.

Edit: The TEXT often contains additional lines of info and other text, making it VERY difficult to parse.

 

I would like to know how to insert the whole text block as one item in the invoice and set the TOTAL AMOUNT value as the item price (and use no VAT, so it becomes the final price of the invoice). This is a bit messy, but the important thing is that we can still see/search the past invoices of a client and have an idea of the invoice data. It will be a bit messy that every invoice has only one invoice item in it.

 

Problem 2:

In the old db I have a column PAYMENT_CODE which is blank or set to 'B' if it is payed. When I import the data, can I use if/else structures to set the paymentstatus to payed if the value = 'B'? How would this be done in an import script in Filemaker?

 

Problem 3: 

How do I insert a default value for columns that I don't have data for while importing? For instance, the VAT checkbox has always to be checked (= value 1), the VAT amount is always 0,21, ...

[comment]

I would like to know how to insert the whole text block as one item in the invoice and set the TOTAL AMOUNT value as the item price (and use no VAT, so it becomes the final price of the invoice).

 

I am not sure I follow this fully. I guess you want to import the old invoices twice: once into the Invoices table, and once into the LineItems (what you call Invoice_data?) table. In the second import, map the NR field to InvoiceID, the TEXT field to ItemDescription and the NETTO field to Price (again, these are guesses. since we have no description of the fields).

 

 

In the old db I have a column PAYMENT_CODE which is blank or set to 'B' if it is payed. When I import the data, can I use if/else structures to set the paymentstatus to payed if the value = 'B'? How would this be done in an import script in Filemaker?

 

Immediately following the import (while the found set is still the imported records) you could replace the field's contents with a calculation. Of course, you can do this at any later time by finding records that contain "B" in the field.

[JSS]

I am not sure I follow this fully. I guess you want to import the old invoices twice: once into the Invoices table, and once into the LineItems (what you call Invoice_data?) table. In the second import, map the NR field to InvoiceID, the TEXT field to ItemDescription and the NETTO field to Price (again, these are guesses. since we have no description of the fields).

 

Thanks. Will try to change the payment status after import.

 

I'm working with Filemaker in a different language, so these names are all translations, which can be confusing (sorry for that). 

I'm working with the default invoice solution in Filemaker Pro 13. If I go to the invoices layout, I can add a new invoice. You choose or enter a customer name, and start adding items/products/services as rows in the invoice, each with their price & discount. That's what I mean with items in the invoice. And because in the old DB I have all my sold items in a single plain textfield stored in the old database, It's hard to distinguish different items and their price. That's why I would store all the text as a single "product/item" in the invoice. 

 

I can already import the basic invoice data such as the invoice number, date, customer nr, ... (= without the actual products/items with their prices).

It makes sense to do a second import, but I can't figure out how.

How do I do the second import to the other table? When I'm on the invoices or invoices detail layout, I can only import to the invoices table. And not the LineItems (or translated from my language: Invoice data table), because every other table is greyed out. Seems like Filemaker can only import to the table of the layout that's currently active.

[comment]

You choose or enter a customer name, and start adding items/products/services as rows in the invoice, each with their price & discount.

 

You add these items in a portal - which means every row is a separate record in a related "Line Items" table. This is an important aspect of Filemaker (or any relational database) - make sure you understand this before proceeding.

 

 

Seems like Filemaker can only import to the table of the layout that's currently active.

 

Yes, if you are importing manually. So just move to a layout of the Line Items table before trying to import.

[JSS]

Yes I do know what's going on with the invoices and their records (I use MySQL frequently).

There's the invoices table (or whatever it's called in the english Filemaker Pro 13 invoice starter solution) with has the basic information about the invoice like date, invoice number, etc...

And then there's an invoice data table (or line items) which has all info about items added to an invoice (the price, the item name, a relation number to the invoice by using the invoice NR) etc...

 

However, there's no layout that uses line/invoice items.

I created a new layout that uses the invoice items table, and I could import them. Thanks! I find this a bit strange though, creating a GUI in order to import/modify data?

[comment]

You should have at least one layout for every base table you have, because going to a layout is how you establish context - and context is very important in Filemaker. it's actually not that important when importing (because with scripted import you do not need to go to a layout of the target table first), but in practically every other aspect the current layout makes a huge difference, for example:

Set Field [ LineItems::Price ; 123 ]

does one thing if you're on a layout of LineItems (sets the field of the current record), and a completely different thing if you are on a layout of Invoices (sets the field of the first related record in LineItems, potentially creating a new record if one doesn't exist).

[JSS]

I understand it. Found it strange at first that I needed to create a new layout, one that was not included in the default invoice solution of Filemaker.

It works totally now, some values only need some cleaning (remove the first couple of blank lines from a text field, will look into the TRIM calculation?), and setting the taxable field to 1 with the replace fields functionality.

 

I will try to look into scripting to write an import script to automate this all. Thanks for your help