Jump to content

JMW

Members
  • Content Count

    25
  • Joined

  • Last visited

Community Reputation

0 Neutral

About JMW

  • Rank
    member

Profile Information

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

FileMaker Experience

  • Skill Level
    Intermediate
  • FM Application
    17

Platform Environment

  • OS Platform
    Windows
  • OS Version
    10

Recent Profile Visitors

1,637 profile views
  1. Wim, It turns out that your solution was correct! The website I was interfacing with had stopped accepting requests from our userid. Does this imply that a field can't be used for the base 64 encoded key and used in place of the variable?
  2. Wim, Thanks for the tip. It didn't seem to solve the problem in the long run. It worked for awhile and then stopped, which seems weird.
  3. Hmm ... thanks for the input Wim. I went back and looked at my call to Insert from URL and now have the following code: Insert from URL [Select; With dialog:On; Trans::_temp field; "https://sandbox.services.apexedi.com/api/v3/claims/submit?vendorSiteID=<our id>,type=medical; cURL options:"--location --dump-header $curlHeader --show-error -o $curlOutput -X POST --header \"Authorization: Basic $apexBase64EncodedKey\" --header \"Content-Type: application/json\" --data & @$claimDataJSON" I no longer get the message "The request resource does not suport http method Get" but it does redirect it to a production url instead of the test sandbox and says I'm not authorized. Which is true. Does anyone know why this is happening? 1627 HTTP/1.1 401 Unauthorized Cache-Control: no-cache Pragma: no-cache Content-Length: 41 Content-Type: text/plain; charset=utf-8 Expires: -1 Server: Microsoft-IIS/8.5 WWW-Authenticate: Basic Scheme='OneTouchWebServiceV3' location='https://production.services.apexedi.com/' X-AspNet-Version: 4.0.30319 X-Powered-By: ASP.NET Date: Mon, 11 Nov 2019 23:37:57 GMT Keep in mind I can post to the sandbox url using curl and send event. So either I am or Filemaker is doing something different/wrong on the Insert from Url call. Thanks for everyone's help. Hopefully, I can be helpful to someone sometime.
  4. Thanks for the input! I will have to look at the existing table initially created for a specific API and see if I can make it work as a preferences / setting table. Currently, there is only one application using it. The other two have their data hard-coded and assigned to variables since they were written for specific customers and haven't been made widely available yet. I did this with knowing that at some point I was going to have to store the data in fields in a table. The software I write for has a somewhat interesting model. We have 150+ offices who have a version of our software installed and each office's version has been customized for their specific needs. But, I know that over time more and more offices are going to want to use the software I have been writing to interface with 3rd party software so I'm trying to figure out how to design and code for ease of use and support in the future. From your comments it sounds like I should figure out how to make a single preferences / setting table and figure out how to make it work for all the interfaces. One more question, should I try to modify the existing preferences / setting table to be more generic or just create a new preferences / settings table and use it in the future? The field names in the existing table has fields names specifically for the application it is interfacing with. Using those names in scripts for other APIs would make the code read strangely, if that makes sense. These may seem like simple questions but I don't have many people to talk with about database design questions. Again, thanks for all your input.
  5. Things that are required to interact with the 3rd party api. For example, the website url, and any information that doesn't change that would be required. I have a couple of items I can use as an example curl -o apexEdiOutput.txt -X POST https://sandbox.services.apexedi.com/api/V3/claims/submit?vendorSiteId=<vendorSiteId> --header "Authorization: Basic <base 64 encoded keyword>" --header "Content-Type: application/json" -d "@<path and filename of json>" https://rx7.drfirst.com/sso/portalServices?rcopia_portal_system_name=<vendorname>&rcopia_user_external_id=<external userid>&service=rcopia&action=login&rcopia_practice_user_name=<practice username>&startup_screen=patient&rcopia_patient_id=<patientnumber>&close_window=n&allow_popup_screens=n&limp_mode=n&time=<GMT>&MAC=<base64encodedkey> In the first example: production and test urls, site Id, base64 encoded keyword, (made up of vendor key and vendor password), path and file names for input and output files. In the second example: production and test urls, practice name, vendor name, vendor password, practice user name, base 64 encoded keyword (madeup of vendor name and vendor password), xslts that will be used to create the xml to send to the website, API version, calling application. I am also wondering about how to save the keywords ... vendorSiteId, rcopia_portal_system_name, etc. They will probably never change so I could just hard code them ... We are moving on to our fourth integration with 3rd party software and I know of several more that are on the wish list, mostly health insurance claims processing clearing houses. Each was implemented differently ... and I think I should work toward a uniform implementation for ease of support. Thanks again for any and all help and insight.
  6. I find myself writing more and more code to interface with 3rd party websites (databases) via RESTful type APIs and importing and exporting data using XML and JSON. It is a Filemaker application written for the health industry and contains patient information, medical records, insurance records and billing and accounting data. Before I continue further down this road I am wondering if anyone has any ideas regarding whether I should create a single table with one record for each 3rd party application or a single table for each 3rd party application. Or does it matter in the long run? I can see that there are pros and cons to each. The single table would require field names be quite generic and possibly not truly descriptive of the data they contain where as multiple tables with a single record would allow descriptive names but only contain a single record. Any insight would be appreciated. Before working on this software I worked in the engineering world on computer aided design software. I wrote lots of code but did not deal with databases. Therefore, I do not have a deep understanding of the ramifications of a single table for multiple applications vs multiple tables for each application.
  7. I am using FM 17 and am trying to submit some JSON to an 3rd party website to create and insurance claim. Below is the curl code I am able to run from the command line. I tried using Insert From Url to do the same thing and I am unable to get it to work. I get the following message back when I use Insert From Url: "Message": "The requested resource does not support http method 'GET'." I need both the information after the question mark on the url and the data after the -d tag. I think I read somewhere that if a ? and information is included after the url that Filemaker's implementation of Insert From Url assumes a get. I need to be able to have both and do a post. Am I missing something? Any help would be appreciated. I can use Send Events but would rather use Insert From Url curl -o apexEdiOutput.txt -X POST https://sandbox.services.apexedi.com/api/V3/claims/submit?vendorSiteId=<vendor site id> --header "Authorization: Basic <base 64 encoded key>" --header "Content-Type: application/json" -d "@<path name and file with json>" Thanks!
  8. 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.
  9. 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.
  10. 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.
  11. 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.
  12. 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.
  13. 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.
  14. 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.
×
×
  • Create New...

Important Information

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