8 Replies Latest reply on Aug 9, 2013 12:51 PM by philmodjunk

    Show multiple information from related files

    vincentg

      Title

      Show multiple information from related files

      Post

           Hi,

           Trying to go futher in my usage of Filemaker Pro 12

           I have a database with multiple files 

           Files for Invoice, product, product_categories and product_subCategories

           In the product layout I would like to link using an IdKey and show description for Categories and subCategories, so far it work for Categories but not for SubCategories. Choice is made using list that show the good value but layout is not updated.

           In the invoice layout I have lines for Invoice_Detail that need to show product but also product_categories and product_subCategories

           I have try many combination but all fail.

           Thanks for suggestion or example available

           Vincent

            

        • 1. Re: Show multiple information from related files
          philmodjunk

               Why multiple files? That can work, but complicates the design of your solution. It can be simpler to keep all of these tables in a single file.

               What relationships have you defined linking these tables?

               The classic relationships for invoicing would look like this:

               Invoice----<Invoice Detail>-----Product

               Invoice::__pkInvoiceID = Invoice Detail::_fkInvoiceID
               Product::__pkProductID = Invoice Detail::_fkProductID

               Where each record in Invoice Detail documents the purchase of a qty of products on a specific invoice. Typically, a portal to Invoice Detail is placed on the Invoice layout.

               How you link in the other two tables will depend on the nature of the data in these two tables and how they need to link to a record in (I assume) Invoice_Detail.

               Does a given product match to one and only one record in product_categories? Does a given product match to one and only one record in product_SubCategories?

               What data from these two tables needs to appear on your layout?

               Do you need to use this info to control the values in value lists? (Select a category to get a list of the sub categories for that category, select a sub category to get a list of products from that sub category....)

          • 2. Re: Show multiple information from related files
            vincentg

                 The ralationships you describe for invoicing are what I have

            Invoice----<Invoice Detail>-----Product

                 and Product >----Categories-----<SubCategorie

            it will force to have those relationship Invoice Detail>----Categories2 and Invoice Detail>----SubCategories2

            In Product I show just the names of Categories and SubCategories same for Invoice Detail

            In the Invoice Detail I need to be able to select Categories and SubCategories for unspecified product that are not define in product not regular product (enter manually)

            So far I have work in the Product layout and the Sub categorie ID is ok but not the display SubCategorie Name.

            • 3. Re: Show multiple information from related files
              vincentg

                   my relation schema

                    

              • 4. Re: Show multiple information from related files
                philmodjunk

                Categories2 and SubCategories2 are Tutorial: What are Table Occurrences? that refer to the same data source tables as Categories and SubCategories.

                     Categories2 should not be needed. You can add fields from Categories directly to your portal to Invoice Details and they should display the correct information.

                     An added occurrence of SubCategories will be needed so that you have a direct link from the Products Table occurence to exactly one record in SubCategories

                     Invoice Details>------Products>------SubCategories 2

                     Products::fkidCategorieSous = SubCategories 2::pkidCategorieSous

                     That will allow you to add fields from SubCategories 2 to the portal row.

                • 5. Re: Show multiple information from related files
                  vincentg

                       Not sure to understand or there is a problem

                       Here is the structure, with links

                  Product >----Categories-----<SubCategorie

                  Product >----SubCategorie

                       In th layout when selecting SubCategorie in the menu the the name display is not updated, by showing the pkIDSubcategorie i see that the value don't match so the value show is the one from the current SubCategorie

                       i choose 12 in product and the name display is the one from 9 the first matching the selected Categorie

                  • 6. Re: Show multiple information from related files
                    philmodjunk

                         My mistake on saying that you don't need Categorie 2. I see the issue there. You can either add fields from Categorie 2 to your portal row or you can add a Produit 2 linked by the same match fields as Produit and then link Categorie 2 to it by the idCategorie fields. This second option eliminates teh need for using the fkCategorie field in Detail_Facture to match to a record.

                         You can add fiels from CateorieSous 2 to your portal row, but no value will show until you enter/select a matching value in fkidCategorieSous.

                          

                    • 7. Re: Show multiple information from related files
                      vincentg

                           1st Thanks a part of the solution was make possible by your comments

                           I made a simplified version of my database, was thinking of uplosding (not possible)

                           If I have the product working the invoice is not working then fix the Invoice and the product layout is not working

                           Just the sub categories is creating the problem

                           In the Product layout the subCategorie if choosen from a list fromSubCategories for those matching Categories and use the SubCategorie Product to display values.

                           On the invoice I have CategorieInvoice and CategoriesSub Invoice, the list for the last doesn't adjust or is empty. I have try various combination with no success except when I put match to Categories for matching, but that got product to break

                           obviously I get something wrong!

                      • 8. Re: Show multiple information from related files
                        philmodjunk
                             

                                  the list for the last doesn't adjust or is empty.

                             And what list is that? A conditional value list?

                             If that's what you mean, I can see whay a conditional value list of sub categories would work for products or invoices but not both. The "starting from" table occurrence you'd specify and the table occurrence of sub categories that you'd specify as the source of the values will not work for the other. But you can define two conditional value lists with different "starting from"  and value source table occurences specified and then one can be used on each layout.