Data Migration

[LaRetta]

I am currently converting data from an old DOS program (AccPac sub-module called MedPac) to FM. Current structure consists of four dbs:

Services:Service# (CaptKurt's Primary) > Invoices:Service# (one to many). I have manually matched these two dbs so I now have the Invoice number to work with to establish the relationships in the remaining dbs:

Invoices:Invoice# > LineItems:Invoice# (many-to-many)

LineItems:Invoice# > Payments:Invoice# (many-to-many)

I used Invoice# because that was the only unique identifier in these raw data sets.

I am unclear on the BEST (and quickest) approach (as I'm on tight deadline) to complete this connection. I have two questions for the forum:

1) I am unsure whether to join the Service# down through the entire 4-levels or whether to use Invoice#. Service will have many Invoices per Service -- and each level below will be many-to-many. Which Key should I join at each level to allow for this?

2) IF, I use Service# throughout the levels, what would be the best way to assign them. My thinking is this: Invoices already contains the Service #, so in LineItems (since it's related to Invoices:Invoice#), should I use a SET script something like: SET(LineItems:Service# Invoices::Service# and ... then repeat that process in the Payments db as SET(Payments:Service# LineItems:Service#)?

I am having trouble picturing the correct connections and the easiest (and quickest) way to accomplish assigning these relationships. I hope I was clear with my situation and I appreciate your input!

[CobaltSky]

I suspect that tying your data sets together via Service# will not be ideal.

For one thing, I take it that an invoice may include a number of services, and a single Service# will not always have meaning in relation to an Invoice.

Moreover it is customary for payments to be reconciled at the level of invoice rather than line item - even part payments are generally held against invoice rather than item. Therefore it may only be at the level of Line Items that Service# has a defining meaning.

What I think will be happening is that at the point when an invoice is generated, a number of services will be grouped and output to an invoice (these may even in some cases include services provided to different clients, for whom the same payor is to be billed?).

In this process, an invoice record will be created, and line items will also be created - one for each service. The LineItems will hold an Invoice# and a Service# - and in that sense will provide a connection between the Invoices and Services dbs - however it may be desirable to also capture the invoice number and write it to the corresponding records in Service at the time the invoice is generated. Where a service has multiple invoices generated, each of the corresponding invoice numbers can be captured at the point when each invoice is raised (eg using a multi-key technique).

If I am correct in surmising that payments will be reconciled against their corresponding invoice, then Invoice# will also provide the key for linking of the Payments table. Even if there is a desire to track payments back to individual line items, it is through invoice muber that the line items for a payment would be be accessed.

So invoices sit in the middle, and everything else pivots around them. The operational script which will be required for ongoing use of the data after migration is complete will manage the poplation of invoices and line items with corresponding batches of Service records, placing the current invoice number into all three files.

So, if I understand correctly, it is Invoice# that will be best suited for use as the key which locks together all the disparate sets of data.

[LaRetta]

Thank you. That is good to know and that is how I currently have the 3 dbs -- Invoices, LineItems & Payments -- all joined on Invoice #. A few points of clarification, if I may

[CobaltSky]

1) ... in Invoices (since I will have the relationship with LineItems set to ?allow creation of related record?, as each Line# in an Invoice is added, it will create a LineItem:Invoice#, LineItem:Service# and LineItem:Line#.

The "Allow Creation..." mechanism will write the key field to the related file. In this case, that will deal with the LineItem::Invoice# field. The LineItem::Line# field will be able to be set by auto-entry (check out the 'CustomSerial' demo from my web site to see how this can be set up). That still leaves Service#, which will be different for each record and will not be able to be auto entered. For this, your invoice generation script will do the magic - working from the invoice number, it will extrapolate line numbers and cycle through the lines (eg using a loop, setting each of the LineItem::Service# fields to correspond to one of the services in the current batch being generated).

2)Payments:Invoice# should be left joined to LineItems:Invoice#; because, in our situation, we need to track payments per LineItem. For instance, one day?s service will not be paid and we need to track what wasn?t paid and why. With the variations of criteria (ProcedureCodes & Contract DateRanges, it is important to apply Payments to each service (I strongly believe). I envision receiving a payment from OMAP, finding the Invoice#, displaying on a form in Invoices, which will list a portal (?) of LineItems, in which Payments can be applied toward, thus tying Payments directly to Invoices (but itemizing through LineItems).

Well, when an invoice3 is paid, it will be possible to see this from the level of each item in the line items file, and also (if desired) against each service# in the Service file that was on that invoice. But that doesn't require that you reconcile payments below the level of invoices. Reconcile at invocie level and then draw the results of that down onto the Service and Line Items dbs - that is what I would suggest.

3) If I understand what you are saying, my current join of Service:Service# > Invoices:Service# is incorrect because, although any one Invoice can only contain one Client, it may contain several Services. And you are suggesting that Service be joined to Invoices on Invoice#, which will mean that I need to add an Invoice# field in Service. But, Ray, I'll have several Invoices for one Service!?!

Yes, Service can join to Invoices on Invoice#. Where there are several invoices for one service, each invoice number can be added on a new line in the invoice# field in service. This is what is called a multi-key field, and establishes a relationship simultaneously to each of the listed invoices.

Are you suggesting this approach because, the relationship in Service to Invoices will ?allow creation of related record and, this relationship, as each Service record is created, will create a PRIMARY or original Invoice in Invoices. And subsequent invoices pertaining to that related service will be generated from within Invoices (as per your multi-key suggestion).

No. "Allow creation..." does not mean 'force creation'. However, rather than being created via the relationship (which makes no sense, since there is no one record in Service which can take precedence over others as the trigger to 'claim' the job of making an invoice record), I believe that your invoices should be created by script from within the Service db, as part of a batching process which gathers a group of client service records, creates a new invoice and outputs the service data to corresponding records in the LineItems file to complete a single invoice.

Subsequent invoices can then be generated from within the Invoice file by a second script which writes the additional invoice number back to the multi-key Service::Invoice# field.

4) It sounds like I will still want to capture Service# in LineItems regardless so, for the purpose of this migration only and, since I don't understand your Matrix model, I should leave this relationship (Service:Service# > Invoice:Service# intact until I complete the following sequence: a) Create an Invoice# field in Services. : SET Service:Invoice# to Invoice:Invoice#, c) Use SET to set LineItems:Service# to Invoice:Service# and d) Set Payments:Service# to LineItemsService#

I can't see that there is any point in having a Service# field in the Invoice file, as it has no meaning there and it belongs in LineItems. There should be no difficulty in writing it direct to the line items file from Service (based on a direct relationship from Service to LineItems, since Invoice# will be in both) without needing to shunt it back and forth through a redundant field in Invoices.

Last comment about re-invoicing (your point of using a multi-key technique): I have considered a sub-set to an invoice #, i.e., Invoice# 28101 SUB 01 (primary invoice) SUB 02 (secondary invoice), etc. Is this what you are describing as a multi-key technique? Do you think I'm getting closer, please say yes

If the 01, 02 etc sub numbering serves a purpose for the people and the business rules then that's fine, but they are not necessary from the point of view of tracking multiple invoices against service. If you use them they could be set up as an alternative to the multi-key approach, but either way, you should adopt a consistent logic throughout the system.

Unless there is a compelling reason to do otherwise, I would be inclined to suggest adopting straightforward sequential numbering for your invoices, and using the relationships (including multi-key techniques as described above) to take care of tracking them.

Hopefully these 'comments on your comments' help to fill things out for you?

[LaRetta]

Yes, you have filled in the pieces for me. I recognized Invoices:Service# could eventually be eliminated. I didn't think about directly relating Service to LineItem (after assigning Invoice# in Service) to pass the Service#. I'm a bit tired but even so, this fits for me. As for scripting this once the migration is complete, well, that's another issue. Oh, what fun I'm having!

I appreciate you taking the time to set me straight!