Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About JMW

  • Rank

Profile Information

  • Title
    Software Development and Suppot
  • Industry
  • Gender
    Not Telling
  • Location
    Utah, USA

Contact Methods

  • Yahoo

Recent Profile Visitors

1,521 profile views
  1. 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.
  2. 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.
  3. 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. 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.
  4. 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.
  5. 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: <!-- some of the data from the attribute tags goes in the Claim table --> <result check_number="01574380" eraid="2182629" paid_amount="34.00" paid_date="2016-02-05" payer_name="" payerid="" prov_name="" prov_npi=""> <!-- some the data from the attribute tags goes in the Claim table --> <claim from_dos="20160201" pat_name_f="BARNEY" pat_name_l="RUBBLE" pat_num="38170" prov_npi="" total_charge="200" total_paid="20"> <!-- some the data from the attribute tags goes in the Line Items table --> <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.
  6. Yes, you are correct the data imports correctly on the test case. Thanks for verifying that for me. So, my xml and xslt are correct. But, the reality is, it doesn't import correctly in our live databases (as in multiple customers) I'm trying to figure out what could be causing the problem.
  7. The files I attached are my production code. So, it seems my Filemaker is behaving differently than yours. That said, do you know what sorts of things in the database could cause this to happen or have any idea how to go about debugging the problem? I would hate to tell the doctors to enter the data manually if it doesn't import correctly. That defeats the purpose of the interface. Thanks for all your help.
  8. Sorry, it is the data, not the format. And, I don't see the first three fields listed at the top of the window. That is my problem. When I do an import I specify matching names and update existing records in found set.
  9. I have an xslt that I modified awhile ago. It imports eye exam data from refraction equipment into our software. It worked fine for a long time. The XML data format was changed in a few elements so I modified the xslt to match the format change. Now when I try and import the data into filemaker the fields are not listed in the import window and, as a result, doesn't get imported. Can someone look at the code and see if there is any obvious reason the fields aren't in the import window in Filemaker? Note: I am not a xml/xslt expert and the original code was written by someone else. I've just modified it over the years. The following elements were originally as follows: <?xml version="1.0"?> <MarcoData-XML> ... <DataSet> <UnaidedVisualAcuity_Data> <DIST_OD> 20/20 </DIST_OD> <DIST_OS> 20/20 </DIST_OS> <DIST_OU> 10/20 </DIST_OU> ... </UnaidedVisualAcuity_Data> ... </DataSet> </MarcoData-XML> The format of their data was changed to: <?xml version="1.0"?> <MarcoData-XML> ... <DataSet> <UnaidedVisualAcuity_Data> <DIST_OD> 20 </DIST_OD> <DIST_OS> 20 </DIST_OS> <DIST_OU> 10 </DIST_OU> ... </UnaidedVisualAcuity_Data> ... </DataSet> </MarcoData-XML> Here is the relevant xslt <?xml version="1.0" encoding="UTF-8" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" /> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="" NAME="Filemaker Pro" VERSION="" /> <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT="h:mm:ss a" /> <METADATA> <xsl:if test="string-length(/MarcoData-XML/DataSet/UnaidedVisualAcuity_Data/DIST_OD) != 0"> <FIELD NAME="Entrance VA OD" TYPE="TEXT" /> </xsl:if> <xsl:if test="string-length(/MarcoData-XML/DataSet/UnaidedVisualAcuity_Data/DIST_OS) != 0"> <FIELD NAME="Entrance VA OS" TYPE="TEXT" /> </xsl:if> <xsl:if test="string-length(/MarcoData-XML/DataSet/UnaidedVisualAcuity_Data/DIST_OU) != 0"> <FIELD NAME="OU Va uncorrected" TYPE="TEXT" /> </xsl:if> </METADATA> <RESULTSET FOUND=""> <xsl:for-each select="/MarcoData-XML/DataSet"> <ROW MODID="" RECORDID=""> <!-- Entrance VA OD --> <xsl:if test="string-length(./UnaidedVisualAcuity_Data/DIST_OD) != 0" > <COL> <DATA> <xsl:value-of select="./UnaidedVisualAcuity_Data/DIST_OD" /> </DATA> </COL> </xsl:if> <!-- Entrance VA OS --> <xsl:if test="string-length(./UnaidedVisualAcuity_Data/DIST_OS) != 0"> <COL> <DATA> <xsl:value-of select="./UnaidedVisualAcuity_Data/DIST_OS" /> </DATA> </COL> </xsl:if> <!-- OU Va uncorrected --> <xsl:if test="string-length(./UnaidedVisualAcuity_Data/DIST_OU) != 0"> <COL> <DATA> <xsl:value-of select="./UnaidedVisualAcuity_Data/DIST_OU" /> </DATA> </COL> </xsl:if> </ROW> </xsl:for-each> </RESULTSET> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet> See attached XML and xslt below for more information. NOTE: I changed the .xsl to .txt so it would upload. RT5100Output.xml RT5100-xsl 4 import-without habitual.txt
  10. GisMo, No I do not have a copy of Filemaker Advanced. I think it's time I ask for one though. I tried creating a table relationship but it didn't seem to work. Bruce, Sorry my script is hard to follow. I'm learning that Filemaker will do some of the loop control for me. I come from non-database coding background and am not used to these features. Thanks for pointing out the conflict with the Enter Find Mode [restore] and Perform Find[restore] It turns out that was part of my problem. I can't see how I can use a Set Field for the find. I'm trying to find all CL_Include[1] fields that aren't blank, so I end up with a list of found records. I then compare the data in the CL_Include fields to part of a string indicating which contacts a patient wears. When I find a match, I copy data in the CL_Include record to a line item on a transaction. The problem is that the data in the CL_Include field becomes my search string and I have to cycle through each of them until I find a match. I also learned that script I'm rewriting is so old it was written before Filemaker had variables. So, instead of using SET FIELD to pass a value to the calling script I used a variable to return the value. Is this considered good programming in Filemaker? Thanks for your help.
  11. I'm trying to compare part of one string to another. If the sub string matches the string then I need to copy the item code for that lens from the table it resides in to a line item on a transaction. An example of the data for the code I've written: Items::CL_Include[1] = Focus Dailies 8.6 Items::CL_Include[2] = Items::CL_Include[3] = Items::CL_Include[4] = Items::Item Code = c8 Trans::ContactLensR = Focus Dailies 8.6 some contact Rx info here The problem I'm having is that SET FIELD doesn't copy the data from the Code::Item Code to Trans::St CL store OU field Can someone tell me what I'm doing wrong? The existing script uses SET FIELD [ Case( hardcoded partial contact lens names; hardcoded item code) ] This means contact lenses and their codes cannot be modified without modifying the script. When users modify the names and codes it breaks the script. I'm trying to rewrite the script to eliminate the hard coded stuff in the current script. If I Items::CL_Include[x] matches a substring of $contact lens then I know I have a match and need to copy the contact lens item code to a field in the transaction file. Below is the my script: Set Variable [$contactLens; Value:Trans::ContactLensR] //see above for example of data for this field New Window [Name: "Contact Lens Pull Down Data"; Height: 750; Width: 750; Top: 50; Left: 50 ] * my layout displays all four fields in the repeating field CL_Include and the corresponding Item Code from the Items table. * these are displayed in a portal on the layout Go to Layout ["Contact Lens Pull Down String" (Trans)] Enter Find Mode [Restore] Perform Find [Restore] Enter Browse Mode[] Set Variable [$numRecords; Value:Get ( FoundCount )] Go To Record/Request/Page [First] Set Variable [$numRecordsSearched; Value:1] Set Variable [$match; Value:"False"] Loop Set Variable [$wordCount; Value:WordCount( Items::CL_Include[1] )] If [LeftWords( Items::CL_include[1]; $wordCount ) = LeftWords( $contactLens; $wordCount )] Set Field [Trans::St CL store OU; Items::Item Code] *** problem here *** Set Variable [$match; Value:"True"] End If Exit Loop If[$match = "True" or $numRecords = $numRecordsSearched ] Go to Record/Record/Page [Next] Set Variable [$numRecordsSearched; Value:$numRecordsSearched + 1 End Loop I'm a programmer but I have very little database experience. After looking at lots of posts on here I think I can simplify my Loop but my concern at this point is that the Set Field is not working and I need to be able to assign Trans::St CL store OU the value from Items::Item Code so it can become a line item on a transaction. Thanks for your help.
  12. So this may be a novice problem but I don't have enough experience yet to know how to do it using a Filemaker script. If I can't do it using a FM script can I call program in another language from a FM script that can do it. I don't have control over what is installed on the computers our software is run on so I'd like to do it without having to install yet another piece of software to make it work. I need to take the XML created by FM Export and convert it into another XML file and save it to a specific location on the local computer. I want to do this by taking the XML created by the FM Export execute an XSLT file on it and get a different XML file as output then save it. This XML file will then be used to transfer data onto a piece of hardware attached to the computer using another interface. Most of our customers run Filemaker Pro NOT Filemaker Server. Thanks for your help!
  13. Thanks for the information. At least now I know that it can't be done so I'll stop trying to figure it out. I've tried a variation of the first suggestion. I assigned the fields to local variables then ran the xslt. When I reassign the values of the variables to their respective fields the numbers loose the 0's in the decimal places. For example, -2.00 becomes -2. In my case the decimals are required. I haven't tried the second suggestion. I'm wondering if I go to all the work if I'll loose the 0's in the decimals. So now I have to decide whether I add code to fix the missing 0's or just have two xml files. I know the easiest thing to do is have two xml files but I'm not sure it's best from a maintenance standpoint. Again, thanks for your help.
  14. Hi, I'm working on an xslt that will import data into Filemaker I am working with a single existing record that may or may not contain existing data. If certain fields contain existing data I do not want to write over them when I import data from the XML file. So, I would like to pass a value to the xslt that will tell it to skip the data I do not want updated. Is there a way to do this? Filemaker script: check to see if field has data if data = true somehow pass the parameter to the xslt XSLT get the value of the parameter that was passed from the filemaker script if data = true do nothing with xml flags else process flags I can't just put an empty <DATA> tag in the xml because it writes over the existing data. The only way I've found to do this is to use two xslt files. Id rather not do this because then I have to maintain two of them. Thanks for any help you can provide.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.