6 Replies Latest reply on Aug 12, 2011 9:35 AM by philmodjunk

    Using External SQL tables/records, but where to put my new extra data?

    TimWatson

      Title

      Using External SQL tables/records, but where to put my new extra data?

      Post

      I am working on a "Quality Control" solution for FMGo on the ipad, using our company's SQL Tables (from SAP Business One). I have live connections with four SQL tables (Products, Purchase Orders, Manufacturers, and PO Line items). All of the relationships are good, and this works great as a closed solution for viewing information. However, I need to add a local FM table (or several fields, and container fields) that will house some qualilty control information for each record in the PO Line Items table, and will be directly related to the SQL table "PO Line items). The problem I can't figure out is how to create these fields, because I can't/don't want to make these new fields part of the SQL table. So, the question is, "is it possible to create fields (other than calculations/summaries) offline/local inside of filemaker in the SQL table occurence? Of if not, (which I believe is the case), how can I create a local FMP table that actually uses the records from the SQL table "PO Line Items"?

      Basic problem that I need to resolve is:
      I don't know where to put the quality control information that we collect on the iPad in FMP. It can't go into the SQL table, but it has to use the records from the SQL Table "PO Line Items".

      Can you help?

        • 1. Re: Using External SQL tables/records, but where to put my new extra data?
          philmodjunk

          Assuming you have the necessary Primary Key field in the SQL table, you can define a separate table in FileMaker and link it to the external data source table in a one to one relationship using that primary key field. On a layout, you can put fields from both tables together as though they are from a single table in most cases.

          • 2. Re: Using External SQL tables/records, but where to put my new extra data?
            TimWatson

            If there isn't a unique/serial for these records in the sql table, can I calculate a unique field (by concatenating certain fields in the record) and use that as the primary key in the sql table?  I understand about putting fields from both tables on the same layout once my relationships are good.  I'm having trouble with the relationship between the sql and fmg table at this point.

            • 3. Re: Using External SQL tables/records, but where to put my new extra data?
              TimWatson

              Also, should there only be one relationship to the local FMP table from the SQL table?  That being the primary key to the foreign key field?

              • 4. Re: Using External SQL tables/records, but where to put my new extra data?
                philmodjunk

                The only reason I described this in terms of a one to one relationship is that seemed the closest match to adding FileMaker fields to your occurrence to the SQL table.

                AS I understand it, you can relate your external data source table occurrences of this table to other table occurrences in your database much like you would any other table in your file. You just have to either find a way to work with the existing field definitions as created by the other databse software in the external SQL table or use the external table's software to add additional fields that you can use as relationship keys.

                • 5. Re: Using External SQL tables/records, but where to put my new extra data?
                  TimWatson

                  You are correct that I don't want to add the fields/information to the SQL table.  I do want the information to be in a local FMP table.  I'm just a little confused as to how to make the relationship.  Can you advise about this question:

                  "If there isn't a unique/serial for these records in the sql table, can I calculate a unique field (by concatenating certain fields in the record) and use that as the primary key in the sql table?  "

                  • 6. Re: Using External SQL tables/records, but where to put my new extra data?
                    philmodjunk

                    Wouldn't that be something you produce in the SQL table or the query to it? Otherwise, I don't see how this would work, but I'm advising you from the ragged edge of my personal experience. I understand there's a "shadow table" in FileMaker that references the fields in the SQL table. I don't think there's a way to add fileMaker fields to this or you wouldn't be asking this question, but I don't have the right reference material in front of me to verify that assumption. If you can't add fields to it, how can you add a calculation field to produce the primary key?

                    That's why I suggested to either use the fields you already have or to create on in the Source table (or query to it) that you can use.

                    You might get what you want with a multi-field relationship. If a combination of 3 fields in the SQL table define a unique value, then define 3 matching fields in your FileMaker table and link all 3 pairs of fields in the same relationship.