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:
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, ...