Skip navigation
    3 Replies Latest reply on Jul 15, 2011 9:18 AM by philmodjunk

    How can I combine data from two different databases in one portal from a third database.

    CorneliaPoepsel

      Title

      How can I combine data from two different databases in one portal from a third database.

      Post

      Hello, I'm new in this forum and hope to get help with my problem.

      I want to create a database for "estimates".

      In this database I have a portal which should let me choose from a "Product" and also a "Material" database

      as a part of my "estimate".

      The database "material" contains only simple items you can buy and sell.

      I created a value list "Material_ID in my database "Material"

      The database "products" contains products, which are bundles of different materials and labor.

      I created a value list "Product_ID in my database "Products".

      The database "estimates" contains the tables "estimates", "sales orders" "Invoices" and the tables

      "estimate_LineItems"' "SalesOrders_LineItems", "Invoices_LineItems"

      I created the field "List_items" in the table "estimate_LineItems":

      "List_items" - Text - Indexed, Auto-enter Calculation replaces existing value, Evaluate Always

      ValueListItems("Products";"Product_ID")&"¶"&(ValueListItems("Material";"Material_ID"))

      Storage: minimal Automatically, validation only during data entry.

      It shows me only some items from both databases "Material" and "Products", but not all. 

      If i add new Items to the Databases "Products" or "Material" they are not listed in my database "estimates" portal.

      Please give me some help.

        • 1. Re: How can I combine data from two different databases in one portal from a third database.
          philmodjunk

          I can't tell from your initial post exactly how you set this up.

          How have you set up this portal that "shows me only some items from both databases".

          To what table occurrence does it refer? What is the relationship?

          A portal can only list records from a single table so something unusual has been set up if you can see any data from both tables. Most likely, the data is somehow being copied from both tables into a third table and it is not updating correctly.

          But I could easily be misunderstanding what you have set up here.

          BTW, it would greatly simplify your database structure if you combined Material and Products in a single table. Records in this table can use an added field to designate them as either "Products" or "material" so that you can still work with either group of records.

          • 2. Re: How can I combine data from two different databases in one portal from a third database.
            CorneliaPoepsel

            Hy,

            Thank you for your quick response.

            Screen_shot_2011-07-14_at_5.09.31_PM.png

            Hope it will show the screenshot of the relation ships...

            I considered also to go with one table, but it would be much more neat to keep them separate.

            The informations are pretty different from each other...

            I read in an other forum it suppose to go:

            e: Data from two tables into one value list

            Yes. it takes five steps:
            1. define a value list based on field values from book_title in the books file
            2. define a valuelist based on field values from article_title in the articles file
            3. define a calc field in the citations file: 
            List_items=calc, text result = ValueListItems("book_table_name";"book_list")&" ¶"&(ValueListItems("articles_table_name";"article _list")
            4. set up a self-join relationship in the citations file based on the List_items field
            5. define a valuelist based on related values only from the field List_items

            I think I need to know where the data for the calculation is stored, so i can "update" the calculation "List_items" when informations are changing.

            Best would be automatically with changing the value lists... 

            It would be great if I could combine different value lists to one. I think there are more users which would like to do so...

            • 3. Re: How can I combine data from two different databases in one portal from a third database.
              philmodjunk

              Hmmm, so these are values in a Value List, not records in a portal. That's a very big difference here.

              I'm handicapped at the moment when it comes to testing things out in FileMaker as I have it cranking out a report demanded of us by a state regulatory agency (PDF of nearly 300,000 records with many summary fields and sub summary parts.)

              I'm questioning the use of Step 4 here as it may have been needed for the original poster in that other forum discussion but may not apply to your situation here. That self-join may be filtering quite a few items out of your value list here, but I can't test it just yet in a test file to confirm that suspicion.

              More Like This