JMW

Did you get this problem solved? Could you post the path you are using when you try and save the file?

As I am always eager to learn I will look at the card window solution you posted. Thanks for your patience and help. It is truly appreciated! You asked about the relationship between addons and treatments. Hopefully, what you are asking is what I am going to explain. It is my understanding that the attribute ADD_ID=”001” in the <LENS ..> element is used identify which treatments are included or incompatible with a lens. They are identified by the attribute ADD_ID=”001” in the <ADDED_VALUE ..> tag. The diagram you created is how I have it designed with a many to many relationship between Addons and Treatments. (I also have a join table between Addons and Treatments. Yet, it presents the join table problem you mentioned with importing the data.) I am not tied to this if there is a better way. Sorry, I don't understand what you are trying to say here. I am guessing it is because I don't know how to create or use Entity Relationship Diagrams. Database design is new to me. Do you know where I can learn more about it? I've written lots of code but mostly code using existing database structures or code outside of databases that doesn't require what you're talking about.

If you scroll up in the "conversation" in my post on October 9th there is a picture of the current setup of the tables and relationships. I am trying to create an interface between our software and an optical lab. Users of our software will create an order and send it to a lab to order lenses and/or have lenses cut and put in frames. To create the interface I need to redo the way lenses are setup and stored in the system. We receive the lens data in multiple XML files that will need to be imported into our system and made available for use. The high level xml elements correspond to generic lens categories. There are lens types, designs (of the lens itself), lenses available in that design, add-ons or treatments for a lens, then a corresponding list of incompatible treatments for given treatments and finally lens materials. Below is a simple example of the data we receive (and attached as pictures is the XML with the options chosen highlighted in the example): <LENS_TYPES> <LENS_TYPE Description="BiFocal" Code="BFF" /> <LENS_TYPE Description="Progressive" Code="PAL" /> </LENS_TYPES> <DESIGNS> <DESIGN ParameterID="0" Description="SOLA Access" VwCode="ACCESS"> <LENSTYPE Code="PAL" /> </DESIGN> <DESIGN ParameterID="0" Description="SOLA Access 125" VwCode="ACCESS125"> <LENSTYPE Code="PAL" /> </DESIGN> <DESIGN ParameterID="0" Description="Executive Bifocal" VwCode="EX"> <LENSTYPE Code="BFF" /> </DESIGN> <DESIGN ParameterID="0" Description="Flat Top 25" VwCode="FT25"> <LENSTYPE Code="BFF" /> </DESIGN> <DESIGN ParameterID="0" Description="Flat Top 28" VwCode="FT28"> <LENSTYPE Code="BFF" /> </DESIGN> </DESIGNS> <LENSES> <LENS ADD_ID="0140AD0016" Description="ACCESS-50-NONE-NONE-00" DesignCode="ACCESS" MaterialCode="PL-50-NONE-NONE-00" /> <LENS ADD_ID="0140AD0016" Description="ACCESS-50-NONE-NONE-16" DesignCode="ACCESS" MaterialCode="PL-50-NONE-NONE-16" /> <LENS ADD_ID="0140AD0016" Description="ACCESS125-50-NONE-NONE-00" DesignCode="ACCESS125" MaterialCode="PL-50-NONE-NONE-00" /> <LENS ADD_ID="0140AD0016" Description="ACCESS125-50-NONE-NONE-16" DesignCode="ACCESS125" MaterialCode="PL-50-NONE-NONE-16" /> <LENS ADD_ID="0140AD0014" Description="FT25-G7-NONE-NONE-00" DesignCode="FT25" MaterialCode="GH-70-NONE-NONE-00" /> <LENS ADD_ID="0140AD0014" Description="FT25-G5-NONE-NONE-00" DesignCode="FT25" MaterialCode="GL-50-NONE-NONE-00" /> </LENSES> <ADDED_VALUE Description="AD0016" ADD_ID="0140AD0016"> <TREATMENT Code="AR-AR" Description="Standard AR" Type="Selectable" /> <TREATMENT Code="AR-BSIDE" Description="Back Side AR" Type="Selectable" /> <TREATMENT Code="AR-ENDURA" Description="RF Endura" Type="Selectable" /> <TREATMENT Code="AR-KAR" Description="Kodak CleAR" Type="Selectable" /> <TREATMENT Code="AR-KCNC" Description="Kodak Clean N CleAR" Type="Selectable" /> <TREATMENT Code="EDGE-EP" Description="Polish Edge" Type="Selectable" /> <TREATMENT Code="EDGE-ER" Description="Roll Edge" Type="Selectable" /> <TREATMENT Code="SR-SRC" Description="Scratch Coat" Type="Selectable" /> <TREATMENT Code="UV-UV" Description="Lab UV Coat" Type="Selectable" /> </ADDED_VALUE> </ADDEDVALUES> <TREATMENTS> <TREATMENT Description="Standard AR" VwCode="AR-AR"> <INCOMPATIBLE Description="RF Endura" VwCode="AR-ENDURA" /> <INCOMPATIBLE Description="Kodak CleAR" VwCode="AR-KAR" /> <INCOMPATIBLE Description="Kodak Clean N CleAR" VwCode="AR-KCNC" /> </TREATMENT> </TREATMENTS> <MATERIALS> <MATERIAL Description="1.5 Plastic" VwCode="PL-50-NONE-NONE-00" /> <MATERIAL Description="1.5 Plastic SRC" VwCode="PL-50-NONE-NONE-16" /> <MATERIAL Description="Glass 1.70 Clear" VwCode="GH-70-NONE-NONE-00" /> <MATERIAL Description="Glass" VwCode="GL-50-NONE-NONE-00" /> <MATERIAL Description="Polycarbonate SBF LUX" VwCode="PO-58-BLUD-NONE-00" /> </MATERIALS> Given the example data above if I start with a list of: Bifocal Progressive and choose Progressive, the list of available designs should display: SOLA Access SOLA Access 125 Choosing the design SOLA Access would display the lenses: ACCESS-50-NONE-NONE-00 ACCESS-50-NONE-NONE-16 Choosing ACCESS-50-NONE-NONE-00 would list the compatible treatments corresponding to the lens addon id: Standard AR Back Side AR RF Endura Kodak CleAR Kodak Clean N CleAR Polish Edge Roll Edge Scratch Coat Lab UV Coat Choosing Standard AR would remove the incompatibles and present the remaining treatments: Polish Edge Roll Edge Scratch Coat Lab UV Coat The material is selected when the design is chosen. All the choices lens type, design, material, lens and treatments would be retrieved and put in the XML to create the order. Hopefully, this clarifies what I am trying to accomplish. My goal is to set up a well designed database that allows me to create a lens order. I am open to choosing data to create the lens order in the easiest clearest way whatever that may be.

Ok. I am back to working on the setup for this catalog. I found out two things since we talked. Users should not be adding lenses, etc. to the database. All the input should come from importing. And, there are multiple catalogs to be imported. There will be regular updates. You mentioned "not bother adding my own keys." So ... would should there just be many to many relationships between tables? Sorry ... just trying to learn how to do database design correctly?

Yes, that is a correct understanding. What I am asking about is a small part of a larger design. The relationship and XML I asked about is a subset of a lens catalog that will need to be imported. It is an XML file that contains, lens type (bi-focal, etc.), material (glass, plastic, etc.), lenses, and addons. Addons consist of a list treatments that can be used on a lens and the corresponding list of incompatible treatments. I was asked to create the XML to submit an order to a lens lab. That requires that I create the XML for a SOAP request and the XML for a lens order. I soon realized that to create the XML I would have to redesign how lenses are dealt with in our current system. It works fine for orders submitted on paper but not so well with orders created and submitted electronically. If the redesign is done well, it should working with multiple vendors electronic order systems. Below is my database redesign for lenses. It appears that one problem I will have to solve is how to populate the join tables after importing data.

Thanks for the explanation. Using the following example, what I need to do is eliminate incompatible options from a list of treatments so that only the possible treatments are available for selection. Crizal Alize UV Incompatible: Standard A/R Incompatible: Clearsight Claris Incompatible: Crizal Avance UV Crizal Avance UV Incompatible: Crizal Alize UV Incompatible: Standard A/R Incompatible: Clearsight Claris Standard A/R Incompatible: Crizal Alize UV Incompatible: Clearsight Claris Incompatible: Clearsight Claris Clearsight Claris Incompatible: Crizal Alize UV Incompatible: Standard A/R Incompatible: Clrizal Avance UV ESColor Emerald Blue ESColor Emerald Blue Gradient Polish Edge Incompatible: Edge roll and polish Edge Roll and Polish Incompatible: Polish Edge If I choose Crizal UV the list would be narrowed to: EsColor Emerald Blue EsColor Emerald Blue Gradient Polish Edge Edge Roll and Polish And if I choose Polish Edge the list would be narrowed to: EsColor Emeral Blue EsColor Emeral Blue Gradient The treatments chosen for the lens are Crizal UV and Polish Edge. I need to save these in the lense order and export the choices with the lens order in XML (different than the catalog shown above) Each choice reduces the list of possible choices. I am assuming I'll have to figure out how to use the JoinTable. Or am I going about this wrong way? I am basing the design off of the catalog xml but it could be totally off base.

I will have to find out how the catalog is going to be used. It is a completely new module for us. Our current implementation uses a subset of the catalog in an old file containing lots of different data and new lenses are added by each office by hand. (Each customer has it's own copy of our FM solution.) So ... as soon as I say users won't update the catalog ... I know they'll just want to enter the few new lenses by hand instead of updating the whole catalog. But, I could allow that using the many to many relationship too. You mentioned reporting. Do you mean creating FM reports or listing and selecting data that is displayed? Sorry. Just trying to understand exactly what you're saying.

I will take some time and absorb what you said in your post but to help clarify your confusion ... Treat Id is a number (functional key) and used to map a record to the JoinTreatIncomp table. Where as Treat Code is a string that is used in the catalog along with a description of the treatment. The xml I will create to submit a lens/eyeglass order requires both their Treat Code and Treat Description. Here is an example of the data in Treatments Treat Id Treat Code Treat Description Incompat Id 1 AR-ALIZE Crizal Alize is the calculation field for the incompatible list 2 AR-ALIZEUV Crizal Alize UV 3 AR-AR Standard A/R 4 AR-BLUELIGHTAR Empire Blue Light AR 5 AR-BSIDE Back Side A/R JoinTreatIncomp So my join table contains numbers referencing a treatment and it's corresponding incompatible treatment Based on the above data and example is: TreatmentFK IncompatFK 1 3 1 5 2 1 2 4 3 5 Being an intermediate FM scripter and a new FM database "designer" I set up the table and fields based on my current understanding of how this should work. If it's wrong, I can change it.

I am slowly beginning to understand what you are trying to say above. I will need a calculation field in the Treatments table (which I created an named Incompat Id) with the List() function in the calculation. I am still not sure which field it will contain or how to display the list on a layout. Thanks for all your patience and help while we learn.

I am sorry but I don't understand what you're trying to tell me. Does "calculation field in the treatment table" mean the field contains an actual list? I'm guessing it's not the case but I'm not understanding what you mean by list. I included a picture of the portion of my relationship graph we are talking about. I am also guessing that I will need to modify the VWIncompat 2 (it is a 2nd occurrence of VWTreatment) to have Incomp Id linked to Incompat FK and then set to not equal. Thanks for all your patience and help. I truly appreciate all the help I've been given over the years.

Comment, Thanks for the update. As for the XML, it took me awhile to figure it out. The catalog's XML leaves much to be desired. So, I am going to repeat back what I got from your post to make sure I understand it. I will need to duplicate the Treatment table and create a join table named Incompatibilities. This will create a self join for treatments and set up the relationship between treatments and incompatible treatments. I am assuming setting up treatments and incompatibilities this way will allow me to display only the valid treatments and leave out incompatible treatments. I have created many to many relationships before between existing tables without a join table. But, I am designing this "module" from scratch and am trying to create a proper database structure with no duplicate data. It seems join tables are the way to do this. There are a lot of treatments and a lot of incompatible treatments so I'm not sure how a well checkbox would work. One treatment I looked at has something like 67 incompatible treatments. (Although, maybe I don't understand what you mean by checkbox.) So, I am thinking the join table will be the best solution. I also think it would be best based on your comment about reporting. The data in the catalog is going to be presented to users for creating lens and eyeglass orders that can be submitted to a lab. So, displaying the data in the catalog accurately is important. I am guessing that is also considered reporting. I do not know if users will add data. That is something I'll find out later. Hopefully, not often if they do. Thanks for all your help!

Thanks for verifying my setup as correct. I am still pretty new at setting up tables and relationships from scratch. What I meant by duplicate data is that a treatment is also an incompatible treatment so while there is data in the treatment data, that same data will also be in the incompatible table. Thus, duplicate data(?) Hopefully, that makes sense. And, thanks for pointing out that I should only import unique records! Without thinking it through, I'd have imported all the data.

I've mentioned this before. My background is in programming in a CAD/manufacturing environment. So, while I am familiar with coding and have been learning to write and develop Filemaker scripts for several years database setup and relationships are a weakness. That said, I am creating a system to submit lens orders for eyeglasses to a lab. The lens catalog is sent to each doctors office from the lab in xml format. Each catalog can have different content but the XML format is the same. I know how to write the xslt to import the catalog. What I'm struggling with is setting up the tables in a file. I created a file named after the vendor and in it I have created several tables to match the structure of the xml as much as I can. What I am struggling with is setting up a relationship for lens treatments and exclusions based on treatment chosen. Here is a small sample of the xml from a catalog. <TREATMENT Description="Crizal Alize UV" VwCode="AR-ALIZEUV"> <INCOMPATIBLE Description="Standard A/R" VwCode="AR-AR" /> <INCOMPATIBLE Description="Clearsight Claris" VwCode="AR-CLARIS" /> </TREATMENT> <TREATMENT Description="Crizal Avance UV" VwCode="AR-CRZAVCUV"> <INCOMPATIBLE Description="Crizal Alize UV" VwCode="AR-ALIZEUV" /> <INCOMPATIBLE Description="Standard A/R" VwCode="AR-AR" /> </TREATMENT> <TREATMENT Description="Standard A/R" VwCode="AR-AR"> <INCOMPATIBLE Description="Crizal Alize UV" VwCode="AR-ALIZEUV" /> <INCOMPATIBLE Description="Clearsight Claris" VwCode="AR-CLARIS" /> <INCOMPATIBLE Description="Clearsight AR" VwCode="AR-CLEAR" /> </TREATMENT> <TREATMENT Description="Clearsight Claris" VwCode="AR-CLARIS"> <INCOMPATIBLE Description="Crizal Alize UV" VwCode="AR-ALIZEUV" /> <INCOMPATIBLE Description="Standard A/R" VwCode="AR-AR" /> <INCOMPATIBLE Description="Clearsight AR" VwCode="AR-CLEAR" /> <INCOMPATIBLE Description="Crizal Avance UV" VwCode="AR-CRZAVCUV" /> </TREATMENT> <TREATMENT Description="ESColor Emerald Blue" VwCode="TINT-EMERALDBLUE" /> <TREATMENT Description="ESColor EmrldBlu Grd" VwCode="TINT-EMERALDBLUEGRAD" /> I want to set up a table and/or relationship between treatments and incompatibilities so that when a user chooses a treatment for a lens the incompatible options do not display. But an incompatible option is also a treatment. I am contemplating two tables one for treatments and one for incompatibilities connected by a join table since it is a many to many relationship but that requires duplicate data and I'm trying not to duplicate data. Is there another way to do this? If so, what might it be? Treatment JoinTable Incompatible Description +--------- TreatmentFK VwCode VwCode | IncompatFK ---------------------------------- IncompatId TreatmentId --+ Hopefully, this makes sense. Thanks in advance for all your help. This forum has been a life saver over the years. I have learned a lot from it.

I am working on a interface to submit claims to an insurance clearing house. I am able to create the JSON and submit the claim but the clearing house won't accept some empty fields. As a result, I would like to make sure there are not empty values in the required key/value pairs. Is there an easy way to quickly do this after the JSON is built? I realize it could be done when building the JSON but I'm trying to write scripts that only do one thing at a time. Thus, a script to check the JSON before submitting it. If there isn't, I'll just write a script to check all the FM fields before creating the JSON. Thanks for any help you can provide. { "Claims" : [ { "Prov_PayerID" : "APX14116", "Prov_VendorClaimID" : "103", "F00_PayerName" : "ACME HEALTH INSURANCE CO.", "F01A_InsuredId" : "AHI226059876", "F02_PatientNameFirst" : "Deanna", "F02_PatientNameLast" : "Troi", "F03_PatientDob" : "1990-03-29", "F03_PatientSex" : "F", "F04_InsuredNameFirst" : "Deanna ", "F04_InsuredNameLast" : "Troi", "F05_PatientAddress1" : "2336 Betazed", "F05_PatientCity" : "Some City", "F05_PatientState" : "XX", "F05_PatientZip" : "NNNNN", "F06_PatientRelationshipToInsured" : "Self", "F07_InsuredAddress1" : "2336 Betazed", "F07_InsuredCity" : "Some City", "F07_InsuredState" : "XX", "F07_InsuredZip" : "NNNNN", "F11A_InsuredDob" : "1990-03-29", "F11A_InsuredSex" : "F", "F12_PatientAuthorization" : "Yes", "F13_InsuredAuthorization" : "Y", "F21_DiagnosisCodes" : [ "H00014 ", "H47323 " ], "F21_IcdIndicator" : "10", "F24_ClaimLines" : [ { "F24A_DateOfServiceFrom" : "2021-02-25", "F24B_PlaceOfServiceCode" : "11", "F24D_HcpcsProcedureCode" : "92004", "F24E_DiagnosisPointers" : [ "A" ], "F24F_Charges" : 154, "X12_ProviderControlNumber" : "111" }, { "F24A_DateOfServiceFrom" : "2021-02-25", "F24B_PlaceOfServiceCode" : "11", "F24D_HcpcsProcedureCode" : "92250", "F24E_DiagnosisPointers" : [ "A" ], "F24F_Charges" : 68, "X12_ProviderControlNumber" : "112" }, { "F24A_DateOfServiceFrom" : "2021-02-25", "F24B_PlaceOfServiceCode" : "11", "F24D_HcpcsProcedureCode" : "92285", "F24E_DiagnosisPointers" : [ "A" ], "F24F_Charges" : 43, "X12_ProviderControlNumber" : "113" } ], "F28_TotalCharge" : 265, "F31_PhysicianOrSupplierSignatureIsOnFile" : true, "F33A_BillingProviderNpi" : "", "F33_BillingProviderAddress1" : "849 E. 400 So.", "F33_BillingProviderCity" : "Some City", "F33_BillingProviderNameFirst" : "", "F33_BillingProviderNameLast" : "", "F33_BillingProviderPhoneNumber" : "xxx-xxx-xxx", "F33_BillingProviderState" : "XX", "F33_BillingProviderZip" : "NNNNN" } ], "DocumentType" : "MedicalClaim" }

@bcooney it could be more than one piece of equipment but the script can identify how to process the data by looking at the model number of the equipment which is stored in the table. I have the Equipment table in a file that contains data needed to run an office and do eye exams. It is a list of equipment that we support with all the information required for reading the data from the equipment and importing it into an exam. I needed some way to let the script in the exam table (in a completely separate .fmp12 file) know whether or not equipment is set up for automatic data transfer. So, in the equipment table I have a field "In Use" and a field “Avail” in the exam table. The user enters “yes” by the equipment they use in their office. I figured this would be an easy way to identify which equipment in the list is being used. I can then retrieve all the information that is necessary to setup, transfer and import data into the exam record. I only care about the equipment for the data transfer. After that the equipment is irrelevant. I didn’t think of linking the two tables using an equipment id. Maybe because I was trying to identify whether or not equipment is being used and if so which equipment is used in a particular office. I also don’t come from a traditional programming background and am still learning about keys and multikeys, etc. We have over 150 offices that use the same base application. In the past I hardcoded the information for each office but I’m trying to move away from that by creating a table with a list of equipment we support and allowing the user to choose what they have in their office. My goal is to remove myself from the equipment integration loop. Thanks for the ideas. I always learn something new from these discussions.

I read on stack overflow that the tables need to be on the relationships graph to use ExecuteSQL. Is that true? I currently, have the equipment table in a file containing basic information used by the software and the exam table in a file containing information about eye exams. I linked them using a single field "Used" in the Equipment table and "Is Avail" in the Exam table. This allows me to find out if equipment is being used and if it is retrieve the data to set the COM Port and know the format of the data I will be receiving. I'd still like to do it without linking the tables if I can. There really isn't any need for equipment data to be in with exam data. Thanks for all your patience and help.

+----- Code.fmp12e ------+ tables ... Users Doctors Lens Info Drugs Diagnosis Codes ... Equipment *** (Manufacturer, Model Number, Com Port, Speed, Bytes, Bits, ... Installed (yes/no). 1 record for each piece of equipment we support. ) ... +----------------------+ +----Exam.fmp12 --------+ tables ... Exam *** (Eye exam data for a patient. Multiple exams per patient) ... +----------------------+ I am working with an Medical Records / Office Management system for eye doctors, optometrists. We provide our solution to multiple customers across the country. One of the things our software does is transfer data from equipment that is attached to a computer to a patient's exam record so the doctors and staff don't have to enter it manually. Currently, I manually modify the scripts that make the data transfer happen. I would like to set up a table containing all the information for each manufacturer and piece of equipment and have the customer select which piece of equipment they have installed and use the data from these fields in the script so I don't have to modify them manually. In the script I need to know which data from the "Equipment" table to use to set up and transfer the data from the equipment to the exam record but I don't need data about the equipment stored in the exam record. I hope this makes sense. My question is, how can my script acquire that data from the fields in the "Equipment" table without creating a relationship between the "Exam" table and "Equipment" table? Is it possible? If not, what would be a good way to make this possible. My background is not databases, so I still struggle with this aspect of databases. Thanks for all your help!

It is similar but on our system FM Server says webdirect is running. Application (From the FM 18.0.3 documentation) macOS: After a host machine was restarted, Apache web services (httpd) intermittently failed to start automatically. But, on our install I get the message that web services is running but it fails to open the website. Use the filemaker command line interface command fmsadmin to start web services and see if it is running. Does your server say it is? Thanks for the information.

We have a Filemaker 17 Server set up and running on a mac mini (sorry, I can't get on right now to find out which OS it is running). We have set up everything so Web Direct can be used to access a web page for making appointments and filling out forms. We have several different versions running on several different servers (eye doctor's offices) but this one won't work. We have ... Set up web direct on the server, installed the certificate, gotten an external ip address, forwarded the ports, turned off all firewalls and it still doesn't seem to work. I have used fmsadmin to see if web direct is running. When I try to start it, fmsadmin says it's running. Yesterday when I was comparing the processes running on the mac mini FM Server is running on to a different mac mini running FM Server I noticed there are a lot fewer processes related to web direct running on the computer that the web page won't open on. Does anyone have any ideas that can help me figure out what is causing this? And, if you need more information, let me know

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?

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.

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.

Thanks, I will do that.

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.

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.