3 Replies Latest reply on Jan 30, 2009 9:33 AM by TSGal

    JOIN or FIND in portal

    pvhaute

      Title

      JOIN or FIND in portal

      Post

      Hi,

       

      I have an Inventory-file that holds all stock-movements for all products.

       

      From a selected article, I would like to see (in a portal): the quantity available, per delivery-batch (=unique batch number), per type of packaging!

       

      Example:

      Article 1

      Batch X - Container 1.000kg: 5.000kg

      Batch Y - Pallet 200kg: 800kg

      Batch Y - Container 1.000kg: 2.000kg

       

      For this i selfjoined the inventory table (Inv1 and Inv2), based on 'Article', 'Batch', and 'Package' to get the quantities (based on a summary field) from the second instance (Inv2).  This works fine.

       

      I connected the Inv1 to the portal

       

      I run a script to identify unique Batch-Package combinations that are occuring in the inventory table, and perform a 'find' on this identification field (i did this however in another layout as the primary inventory table (Inv1) is not the table linked to the layout where i want to show the portal).

       

      The problem is that i see repetitions of the Batch-Package in the portal, something like this:

       

      Batch X - Container 1.000kg: 5.000kg

      Batch Y - Pallet 200kg: 800kg

      Batch X - Container 1.000kg:

      Batch X - Container 1.000kg:

      Batch Y - Container 1.000kg: 2.000kg

       

      (mind:for the 'repeating' Batch-Package records it doesn't show me any quantities, strangely enough!)

       

      It is as if the portal forgot that a 'find' restricted the number of records that he should show in the first place.

       

      Can anybody help?

      (the actual case contains a lot more tables and instances, but i tried to keep it simple)

       

      Thanks in advance,

      P

       

        • 1. Re: JOIN or FIND in portal
          TSGal

          StPeter:

           

          Thank you for your post.

           

          If you perform a find on a portal, it will find those records in the CURRENT table that meets the criteria in the portal.  It will not limit the number of records in the portal.  It will only omit the records in the current library that don't meet the criteria in the portal.

           

          If you want to limit the number of records in the portal, then you will need a second table occurrence and set the relationship to also reflect the find request.

           

          Go into "Manage -> Database...", click on the Relationships tab and click the bottom left icon to add another table.  Select the Inventory table, and you will see Inv3.  Now, connect Inv1 to Inv3 using the key field (as before) AND the Identification field.  This should get you the results you want.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: JOIN or FIND in portal
            pvhaute
              

            Hi TSGal,

             

            Thank you for your quick reply ... i pondered upon your answer for quite a bit of time, but i think i don't understand it 

             

            May i clarify a bit more?

             

            Actually my table-landscape looks like this (forget about the (simplified) naming i used in my previous posts):

             

            Layout-name: 'InvMgmt-Prod', table linked to this layout: 'Inv6' (which is an instance of the 'Inventory'-table)

             

            In the field 'GLO_ArtNr' (of the table 'Inv6') the user can enter/select an ArticleNumber.

            This field is actually a 'global'-storage-field which is linked to:

            - the table 'Inv7' ... which retrieves (and displays on the layout) the total quantity in stock of the selected product (which is basically the sum of all movements in the inventory of this product, without making a distinction between batchnumbers and package-types)

            - the table 'Inv8' ... which is linked to a portal (on the above layout) that i want to display the quantities in stock (of the selected product) per batchnumber (which is basically the same as a deliverynumber) per package-type

             

            As said, it is in the latter portal that i have an issue:

            As the 'Inventory'-table holds all inventory-movements (all products, all batchnumbers, all package-types, ...), i cannot just list all entries for the selected product and their available quantities (totalled through a 'sum'-field from the table 'Inv9' which is related to the 'Inv8'-table through the 'ArtNr'-field and the 'BatchPackageUnique'-field (more about his field hereafter)) as the 'Inventory'-table can hold multiple transactions for each ArtNr-BatchNr-Package.

             

            Therefore i wrote a script that will limit the records in 'Inv8' to only those that have a unique BatchNr-Package combination for the selected ArtNr.

            This script provides the following sequence of instructions:

            1. goto the layout 'dbase Inv8' (which is a layout that is based on the table 'Inv8') (i do this as it is apparently impossible to operate on an (instance of a) table that is not the basis of the active layout)

            2. find the selected 'ArtNr' (i do this through a global variable, and only to limit already the amount of records that need to be checked (optimize process-time))

            3. sort the found records on the field 'BatchNr-Package' (which is a calculated field, appending the 'BatchNr'-fieldvalue with the 'Package'-fieldvalue)

            4. sequentially run through each record (in the found set) and write the value of 'BatchNr-Package' to the field 'BatchNr-Package-Unique' if this value of the current record is different from the one of the previous record

            5. perform another find on 'non-empty' values in the 'BatchNr-Package-Unique'-field

            6. switch back to the layout 'InvMgmt-Prod' (on which i have the portal)

             

            As the portal is linked to the table 'Inv8', exactly the same table on which i made the 'find'-actions (through the layout 'dbase Inv8'), i would expect to see only the non-blank 'BatchNr-Pachage-Unique' records ... which it doesn't (see previous post), it seems that it forgot that i made the 'find' on the 'Inv8'-records!

             

            I must admit that i still don't fully grasp the impact of the table-access-limitation because of the layouts. so the problem might be there.

             

            (to complete the picture, but further irrelevant for the problem i think: i get the quantities in stock for each batchnr and package-type through a 'sum'-field from the related table 'Inv9')

             

            Does this clarify the situation a bit? ... and (even more important) do you have any advise?

             

            Thanks in advance,

            P

            • 3. Re: JOIN or FIND in portal
              TSGal

              StPeter:

               

              Thank you for the clarification.

               

              A portal allows you to view records in another table that are linked to the current table.  Performing a find on a portal only limits the current table for values that occur in the portal.  For example, suppose you have an invoice table with a portal into an inventory table so you can see the items purchased.  Let's assume there are three records in the invoice table with the following orders:

               

              Invoice 1 - Small widget, medium widget, large widget

              Invoice 2 - Small widget, large widget

              Invoice 3 - medium widget

               

              If you now perform a find on the portal for medium widget, you will end up with two records:

              Invoice 1 - Small widget, medium widget, large widget

              Invoice 3 - medium widget

               
              Both of these records include medium widget in the order.  It does NOT change the link or limit the invoice to the one item.
               
              If you want to limit the number of items in a portal to one object, then you will need to create another relationship to that table that includes the criteria you want to limit.
               
              TSGal
              FileMaker, Inc.