Import xml into two tables with "match key" for one table only

JMW · March 17, 2016

Apparently, I deleted my original post.
Beverly said I should, "update with any auto-entered FM primary key into the child foreign key via the temporary relationship"
I am thinking this is correct but have never done this before.
Can someone help me understand how to do this?

Here is a simplified version of my xml:

<result check_number="01574380" eraid="2182629" paid_amount="34.00" paid_date="2016-02-05" payer_name="" payerid="" prov_name="" prov_npi="">
       
   <claim from_dos="20160201" pat_name_f="BARNEY" pat_name_l="RUBBLE" pat_num="38170" prov_npi="" total_charge="200" total_paid="20">
       
       <charge charge="200" chgid="71245" from_dos="20160201" paid="20" proc_code="92004" thru_dos="" units="1">
           <adjustment amount="160" code="45" group="CO" />
           <adjustment amount="20" code="3" group="PR" />
       </charge>
   <claim " from_dos="20160201" pat_name_f="BAM BAM" pat_name_l="RUBBLE" pat_num="38171" total_charge="720" total_paid="11">
       <charge charge="150" chgid="71248" from_dos="20160201" paid="11" proc_code="76514" thru_dos="" units="1">
           <adjustment amount="139" code="45" group="CO" />
       </charge>
</claim>
</result>

Here are the tables I am working with

Patient table
patient_number - ties to transaction and claims tables

Claims table
patient_number
invoice_number - ties to transaction table

Trans
patient_number
invoice_number - ties to claims table and line items table

Line items table
invoice number - ties to transaction table

I will only be updating existing records not creating new ones.
Oh, I can use the patient_number as a match key in the claims table but don't know how to import into the line items table using the method Beverly described.

Sorry if this doesn't make sense. I am still learning about Filemaker and relational databases.

Edited March 17, 2016 by JMW
Add content

Lee Smith · March 17, 2016

Hey JMW,

Please do not delete a post once it is posted especially if it has had responses to it.

I’m not sure that really did deleted one of your posts. It is hard to determine because all of your post seem to be on the same need, i.e. XML Your questions are starting to look like double posts. Please do not start a new topic when asking additional questions on the same need.

Did you check your Content in your profile? if you find that you didn’t Delete the topic let me know and I’ll merge them.

Lee

JMW · March 17, 2016

Sorry Lee, I looked in the "Content I started" and the old post isn't there so I created this one. I'm still new at this. I didn't delete the post intentionally.

Lee Smith · March 17, 2016

Too bad, hopefully Bev and the others can provide what was lost.

Lee

comment · March 18, 2016

4 hours ago, JMW said:

I will only be updating existing records not creating new ones.

If you really want to do that, you will need to have a matching value that will link the imported source to the target record.

For example, if you want to import from this:

<claim from_dos="20160201" pat_name_f="BARNEY" pat_name_l="RUBBLE" pat_num="38170" prov_npi="" total_charge="200" total_paid="20">

into the Claims table, then you must have a record in the Claims table that is uniquely identified by one of the values here (I don't know what any of them means, I guess it would be either "20160201" or "38170").

3 hours ago, Lee Smith said:

Please do not delete a post once it is posted especially if it has had responses to it.

Lee Smith · March 18, 2016

Ocean West will need to clarify this, but, I agree that maybe it should require a Moderator’s assistance to delete or modify a post after it has been replied to or after a number of hours has past? A lack of replies or a time period can be annoying to those who have answered or are in the process of researching a solution for the OP.

In the past, the original poster could change their post for a short period of time, I don’t remember how much time that was though.

ggt667 · March 18, 2016

Are claim and charge tables also within your FileMaker solution?

4 hours ago, comment said:
If you really want to do that, you will need to have a matching value that will link the imported source to the target record.

For example, if you want to import from this:
<claim from_dos="20160201" pat_name_f="BARNEY" pat_name_l="RUBBLE" pat_num="38170" prov_npi="" total_charge="200" total_paid="20">
into the Claims table, then you must have a record in the Claims table that is uniquely identified by one of the values here (I don't know what any of them means, I guess it would be either "20160201" or "38170").

Only from_dos have data in both claim and charge attributes.

beverly · March 18, 2016

It there is a unique value for a parent, it can be used a primary key (even if temporarily) and used a foreign key in the child records (even if temporarily). Then link these temporary fields to allow an auto-enter primary key in FM parent to be entered into foreign key for child records. I use the same method when there is Excel data that needs to be related and want to use FM's auto-enter serial (or UUID).

You may need to use position() or an element to pass a temporary key, but as this may occur as "not unique" in succeeding imports, it must be cleared after used to set the real keys.

beverly

JMW · March 18, 2016

16 hours ago, comment said:

If you really want to do that, you will need to have a matching value that will link the imported source to the target record.

This may be a naive question but why wouldn't I want to update records?
Sorry, I am still pretty new at developing database code that deals with these type of issues.
I have always updated records instead of creating new records.

4 hours ago, beverly said:

It there is a unique value for a parent, it can be used a primary key (even if temporarily) and used a foreign key in the child records (even if temporarily). Then link these temporary fields to allow an auto-enter primary key in FM parent to be entered into foreign key for child records. I use the same method when there is Excel data that needs to be related and want to use FM's auto-enter serial (or UUID).

You may need to use position() or an element to pass a temporary key, but as this may occur as "not unique" in succeeding imports, it must be cleared after used to set the real keys.

beverly

As I said above these questions may be naive but this is all new to me.
I get all this conceptually but I don't know how to do it.
Sorry, my background is not database programming.

I am thinking of copying the patient id in the claims table to a temporary field in the line items table and then using patient id, date of service and procedure codes as match fields when doing the xml import?
All those fields would be available during the second import into the line items table.
Will this work?

If it won't work, I can think I understand creating a primary key and foreign key (just use set fields and a key) but how do I link them?
It only needs to be temporary link.
Once the data from the insurance is posted it will never be posted again.

Thanks for all your help.

comment · March 18, 2016

1 hour ago, JMW said:

This may be a naive question but why wouldn't I want to update records?

That's not for me to say. I did not understand why you would.

I should add that I also did not understand what your tables do and why they are related the way they are. Or what the data you want to import is and how it relates to the data you have. These are all questions that need to be clarified before we can discuss the technique of the actual import.

Now, if it turns out that you do want to update existing records, then all this talk about relating parents to children (or vice versa) is completely irrelevant. Because if the records already exist, then they will already be interrelated the way they are supposed to be. So all you will need to do is update their values. And what you will need for this is to have a common matching value linking the records being imported to the records already existing. In any and all tables you want to update. That was the point of my previous post.

Edited March 18, 2016 by comment

ggt667 · March 19, 2016

Update is as good a reason as create... The full CRUD( CRUUx ) range is valid depending on use case.

beverly · March 25, 2016

I don't know enough about your databae and XML source to know what will work for "update". If that's the case, I typically import into temporary tables to see if I can "match" the real data on various imported data.

NEI

beverly

ggt667 · March 29, 2016

<?xml version="1.0" encoding="UTF-8"?>

</charge>

</claim>

</charge>

</claim>

</result>

XSLT: parent / claim

<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:eurofxref="http://www.ecb.int/vocabulary/2002-08-01/eurofxref" version="1.0" exclude-result-prefixes="eurofxref">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/*">

</METADATA>

<xsl:apply-templates select="/result/claim"/>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

<xsl:template match="*[name() = 'claim']">

<COL>

<DATA>

<xsl:value-of select="@from_dos" />

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="@pat_num" />

</DATA>

</COL>

</ROW>

</xsl:template>

</xsl:stylesheet>

XSLT: child / charge

<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:eurofxref="http://www.ecb.int/vocabulary/2002-08-01/eurofxref" version="1.0" exclude-result-prefixes="eurofxref">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/*">

</METADATA>

<xsl:apply-templates select="/result/claim/charge"/>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

<xsl:template match="*[name() = 'charge']">

<COL>

<DATA>

<xsl:value-of select="../../claim/@from_dos" />

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="@charge" />

</DATA>

</COL>

</ROW>

</xsl:template>

</xsl:stylesheet>

99346-import-xml-into-two-tables-with-match-key-for-one-table-only.zip

Just wrote this off the top of my head, untested.

Run this as import XML for each table, the first time do create new if you do not already have a table to import to.

Edited March 30, 2016 by ggt667

JMW · March 29, 2016

Thanks for making me think through this again. As I said before, database programming and thinking is new to me. I know my original question was about importing into the database use xml and xslts.

I am working on electronic medical records and trying to update insurance claims with payment information. Claims payment information is what I am working on importing. Unfortunately, a payment can include claims from many patients and many different submission dates.

I have determined the following: I will have to do two imports as the data being imported is in two different tables. My data is indeed related. Now I have to figure out how to use it.

There are three tables: Transaction, Line Items and Claims. Here is how they are related.

Line Items <---> Transaction (related with Invoice Number field)

Transaction <---> Claims (related with Invoice Number field)

Line Items to Claims (no direct relationship)

My first import is into the Claims table. Before the first import I copy the patient number from the Transaction table to a "holder field" and use it as for the "match field" when doing the import. (FM won't let me use the patient number directly.) This leaves me with the found set of claims that were updated with insurance payment information. So, far I have this mostly working.

My problem now, is that I need to get the related Line Items from the Line Items table, copy the patient number from the claims table to the Line Items table and do a second import using the patient number as a match field. I do not see an easy way to do this. I don't want to create direct relationship between the claims table and the Line Items table if I don't have to.

Is there a way to do this?

Any help would be appreciated. If this isn't clear or you need more information, please let me know.

comment · March 29, 2016

I still don't know what the records in each table represent in real life. And how do you want to import the data - update or create new records. And why.

What I see is that each claim has a patient number and something called "from_dos", which in your example is not unique. If you import this into a Claims table, you will be able to relate it to a Patients table (provided you use the same patient numbers) - but not to any specific child of the Patients table (unless the combination of pat_num & from_dos uniquely identifies a record in such table).

Edited March 29, 2016 by comment

ggt667 · March 29, 2016

It's probably a good thing to combine from_dos and pat_num to generate a unique key for the claim - charge "relationship" in which is possible as the charge is below the claim in the hierarchy; if this is the truth throughout the data you are to import, the example I posted above has no practical value, yet it's a reasonable template for you to start from. I'm more curious to which data are to be put on the next level down. adjustment?

Edited March 29, 2016 by ggt667

JMW · March 29, 2016

The database is pretty complex with many tables.

I will try and explain in more detail.

There is a patient table that contains lots of information about patients. There is a transaction table that contains all the transactions that are created in an office. There is a single record for each transaction. A transaction contains lots of data regarding a transaction and a link to line items for the transaction. Line Items are single charges that make up a transaction. So, every line item in a transaction is linked to the line items table by the invoice number. Line items could be a charge for an eye exam, or a pair of glasses, or a pair of contacts, a retinal photo, dilation, etc. Again, these individual charges would be in a different table linked to the transaction by invoice number. For some transactions there might be links to claims that were filed with the insurance. If the patient has more than one insurance there might be multiple claims for a transaction. Or a claim may need to be resubmitted if it was submitted incorrectly.

Transaction Table ( linked to both line items and claims by invoice number)

Invoice number
patient number (this links the transaction to the patient table)
Line items show up on a transaction but are stored in the Line Items table
Claims show up on a transaction but are stored in the Claims table

Claims Table (linked to transactions by invoice number) (Can be more than one claim per transaction)

Invoice Number (links to Transaction table)
Insurance company and insurance information <result payer_name=...>
Check number from insurance company that paid this claim <result check_number=...>
Explanation of benefits for this claim <result eraid=...>
Date claim was paid <result paid_date=...>
Date of Service <claim from_dos=...>
PATIENT NUMBER NOT IN CLAIMS FILE but is only way to match claim data to a patient in the xml <claim pat_num= ...> SO I copy it to a temporary field before the import.
how much was billed to the insurance company <claim total_charge=...>
how much the insurance paid for the claim (all individual charges) <claim total_paid= ...>

Line Items Table ( linked to transactions through invoice number) (can be more than one line item per transaction)

Invoice Number (links to Transaction table)
PATIENT NUMBER NOT IN LINE ITEMS FILE but is only way to match claim data to a patient in the xml <claim pat_num=> I need to know how to get this here so I can do the import.
Amount charged for line item <charge charge=...>
Date of service <charge from_dos=...>
Amount paid for line item <claim paid=...>
Adjustments to amount charged <adjustment amount= ...>
NOTE If group="PR" it is charged to the patient, If group="CO" or a couple others, it is an insurance adjustment
Procedure code <charge proc_code=...>
how much a patient was billed <adjustment amount= ... only when group= attribute is PR>
how much was a deductible <adjustment amount= ... when group= specific value and code= specific value>
how much the insurance paid for charge <adjustment= ... when group= specific value >

I have figured out how to do the first import. That is, I can use the xml obtained from the insurance clearing house and an xslt I have created to UPDATE the claims records. This leaves me with a found set of paid claims. From it, I would like to find the associated line items and do a second import using the same xml and 2nd xslt and update the line items for the transactions. The problem I'm having is that there is no direct link between claims and line items. I need to determine if I can find the line items associated with the claims that need to be updated using the links from claims to transaction to line items.

Hope this helps.

comment · March 29, 2016

1 hour ago, JMW said:

I need to determine if I can find the line items associated with the claims that need to be updated using the links from claims to transaction to line items.

If you want to update existing line items, you must have something in the XML that uniquely identifies an existing line item. It could be a single piece of data or a combination of several. I don't think finding the line items related to the transactions involved in the claims is going to help.

--
P.S. Let me suggest you look at this as a task to be performed manually. Suppose you have an item such as:

And suppose you want to find the corresponding record in line items and update its amount to 160. You need to identify that record using some or all of the following:

code="45"
group="CO"

charge="200"
chgid="71245"
from_dos="20160201"
paid="20"
proc_code="92004"
thru_dos=""
units="1"

pat_name_f="BARNEY"
pat_name_l="RUBBLE"
pat_num="38170"
prov_npi=""
total_charge="200"
total_paid="20"

If you can locate such record manually (using data in the record's fields and in the fields of its parent transaction and/or its grandparent patient), then you can also tell your import step how to do so and automate the process.

Edited March 29, 2016 by comment

ggt667 · March 30, 2016

Remember as long as the hierarchy is the same in the XML, you will be able to generate keys from context.

XSLT: grandchild / adjustment

<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"xmlns:eurofxref="http://www.ecb.int/vocabulary/2002-08-01/eurofxref" version="1.0" exclude-result-prefixes="eurofxref">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/*">

</METADATA>

<xsl:apply-templates select="/result/claim/charge/adjustment"/>

</RESULTSET>

</FMPXMLRESULT>

</xsl:template>

<xsl:template match="*[name() = 'charge']">

<COL>

<DATA>

<xsl:value-of select="../../../claim/@from_dos" />

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="../../../claim/@pat_num" />

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="../../../claim/charge/@chgid" />

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="@amount" />

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="@group" />

</DATA>

</COL>

<COL>

<DATA>

<xsl:value-of select="@code" />

</DATA>

</COL>

</ROW>

</xsl:template>

</xsl:stylesheet>

Edited March 30, 2016 by ggt667

ggt667 · March 30, 2016

On 24 March, 2016 at 0:10 PM, beverly said:

I don't know enough about your databae and XML source to know what will work for "update".

That said importing an XML works just like any other import and conditions can be set in the import interface of FileMaker.

beverly · April 21, 2016

On March 30, 2016 at 1:02 AM, ggt667 said:

Remember as long as the hierarchy is the same in the XML, you will be able to generate keys from context.

<ROW xmlns="http://www.filemaker.com/fmpxmlresult" MODID="0" RECORDID="{position()}">

If you are using position() to set a RECORDID, you may get into trouble. Just leave these blank when importing into FileMaker:

MODID="" RECORDID=""

OTOH, you can use position() to set a temporary primary key field. remember that every import will start anew with 1, so I call it a temporary key for setting relationships to child records to get the real keys immediately after import. Then clear this temporary key field.

beverly

ggt667 · April 21, 2016

37 minutes ago, beverly said:

If you are using position() to set a RECORDID, you may get into trouble.

Which troubles? I would like to know. I have spent quite some time on this FileMaker 5.0 Unlimited - FileMaker 14 Server Advanced and I have not been given any difficulties using this.

My experience is that it's nice to have a counting number there in the case anything goes wrong, but that the import ignores them.

MODID="0" I use as these are all new data to Filemaker.

Edited April 21, 2016 by ggt667

comment · April 21, 2016

43 minutes ago, ggt667 said:

it's nice to have a counting number there in the case anything goes wrong

How exactly will it help you, in case anything goes wrong?

44 minutes ago, ggt667 said:

the import ignores them.

That is correct - which is why it's a waste of time and resources to bother with them (and there are more of these in your stylesheet than just these two).

ggt667 · April 21, 2016

58 minutes ago, comment said:

How exactly will it help you, in case anything goes wrong?

Debugging, easier distinct the different records from each other when there are monotonous data. Clarity is key to not waste time.

58 minutes ago, comment said:

That is correct - which is why it's a waste of time and resources to bother with them (and there are more of these in your stylesheet than just these two).

Care to elaborate?

Edited April 21, 2016 by ggt667

comment · April 21, 2016

53 minutes ago, ggt667 said:

Debugging, easier distinct the different records from each other when there are monotonous data.

The key is not to use monotonous data when testing.

51 minutes ago, ggt667 said:

Care to elaborate?

You mean about the redundant nodes? You can see which are ignored in the help.

ggt667 · April 21, 2016

11 hours ago, comment said:

The key is not to use monotonous data when testing.

That's my point exactly you can use test data as perfect as you like, but when there are actual exceptions 7 months or 5 years down the road, you can not cherry pick your situation.

I also always fill in: <PRODUCT BUILD="LocalPathToDevFolder/opt/dev/TheIntegrationPartner/implementation" NAME="TheIntegrationPartner" VERSION="TimeStampOfImplentation"/>

and: <DATABASE LAYOUT="TheExternalScriptNameInWhichAlsoHasItsOwnLayout" NAME="FileMakerSolution Name"

in production with data that describes the situation, even though FileMaker never bothered to use any of them for anything at all( input or output, ) oh well NAME="filemakerfilename" and PRODUCT is used for exports, but useless in most integrations, yet valid for some web aspects.

Edited April 22, 2016 by ggt667

Ocean West · April 22, 2016

:backtotopic:

let's keep the discussion, productive, informative and encouraging.

beverly · April 22, 2016

@ggt667, the MODID & RECORDID are internal values that we can _get_ and are very useful for web publishing, but simply have no value while Importing FMPXMLRESULT grammar. I do use the position() function and import this into another field as a temporary key. But the readers here need to know that this value is valid at the time of import only.

For example:

If I import 3 records, they will have the values 1, 2, 3 for "position()" upon import.

If I import 4 more records, they will have the values 1, 2, 3, 4 for position() upon import.

These are not the same as the value that FileMaker uses internally for the RecordID. Get ( RecordID )

And with the two imports in this example, you can see that now with 7 records, I will have DUPLICATES even in my temporary key field. These values are only used immediately after import and cleared between imports. Otherwise any child records will have multiple "parents" (not good!).

If the XML source has a unique key value that can be used for matching to update, then it can be used. The internal RecordID should never be used as a primary key and is valuable for update only with web publishing.

beverly

Sign In

Import xml into two tables with "match key" for one table only

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

Similar Content

Important Information