AnsweredAssumed Answered

Copying data from a loosely related SQL table.

Question asked by ianmanning on Apr 11, 2016
Latest reply on Apr 21, 2016 by ianmanning

  FM Pro Advanced 14 , Server 12 (can upgrade)

 

 

I have to transfer data from an SQL data table (which filemaker has, as a related table) to a remote database via a text/xml like file.

This is part of a large project putting data into a CRM database.

I have a line items table containing the detail lines of all orders.

The problem is that the line items do not always include a product many lines have notes in them.

 

For example

 

Record 1

<Order number>  abc

<Line Number> 1

<Product> 1ABC2345

<Quantity> 6

 

Record 2

<Order number>  abc

<Line Number> 4

<Product> 1 piece is ex-stock

<Quantity>

 

Record 3

<Order number>  abc

<Line Number> 5

<Product> balance of 5 in 2-3 weeks

<Quantity>

 

Record 4

<Order number>  abc

<Line Number> 7

<Product> 1DEF9876

<Quantity> 2

 

I need to be able to create a record

Record 1

<Order number>  abc

<Line Number> 1

<Product> 1ABC2345

<Quantity> 6

<Notes> 1 piece is ex-stock ¶  balance of 5 in 2-3 weeks

 

Record 2

<Order number>  abc

<Line Number> 7

<Product> 1DEF9876

<Quantity> 2

<Notes> Please send when complete

 

The line numbers are always increasing between record lines with products on them and the quantity will be blank on the “Notes” type records. There are often gaps in the line numbers and there does not have to be a note style record after a product style record.

 

I think I can do this using a script but would like to know if anyone has a better idea using a recursive calculation or something.

My script idea would be

 

Find all line items modified today (via a date field) (a note live item my change without its product line item changing

Sort by <Order Number> and <Line Number> (this is actually the order they should be in when found as it is the creation order)

 

Loop 1 start

Find all line items for the first /next <Order Number> ( I think I have to open a new window (2) so that I don’t lose the Line items modified today)

Sort by <Line Number>

 

Loop 2 Start

Check it is a product line by the quantity field >0 or not “” 

If it is a note line go to the next record (loop 2)

If it is a product line record the line/record number

Check if the Product order line exists in the Transfer database

Set $notes variable =””

 

Loop 3 start

Go to the next record, exit after last (the product record has no notes unless it has already been through this)

Check if it is a product or note record by the quantity field.

If it is a product record and $Notes is empty, (no notes for the previous product)  Exit loop 3 (go to loop 2 and start again with this record.)

If it is a note record add the note to a $notes variable.

If it is a product record and $Notes is not empty (reached the next product record)

Put $Notes into the Notes field of the record in the Transfer Database

End loop 3

 

Close window 2

 

Go to next record Loop 1 exit after last.

 

I think this should recreate the notes in a product record if either the product or notes record has changed.

Can this be done in a better way?

 

Outcomes