11 Replies Latest reply on Nov 1, 2013 8:50 AM by philmodjunk

    Count portal values and multiply by the quantity

    JessOttman

      Title

      Count portal values and multiply by the quantity

      Post

           Hi,

           Working on windows xp with filemaker 11 pro adv

           I have a portal which gives the accessories for each Hose and the quantity of those accessories needed.

           My portal looks up a accessory table which has the accessory name. I have 6 accessory names and a size per 6 accessories. eg 1" x 6 accessory names, 1.1/2" x 6 accessory names.

           In my portal for a hose I may have :

           1" Procap  selected in the name field and in the quantity field x2 

           1" Enc Seal in the name field and in the quantity field x 4

           Is there a way I can have all my accessory names (grouped by size ) with a total quantity count for the found count

           If I could add the quantity field for each accessory name.

           1" x procap = 20

           1" Enc Seal= 30

           Let me know if I need to clarify anything else 

            

           Thanks

            

            

        • 1. Re: Count portal values and multiply by the quantity
          philmodjunk

               Are you entering 2 in the quantity field or x 2 ?

               Is it of type number or text?

               Much depends on the rest of the layout design requirements. A simple summary report with a sub summary layout part and a summary field may be all that you need. Or you may need something more sophisticated such as that described here: FMP 12 Tip: Summary Recaps (Portal Subtotals)

          • 2. Re: Count portal values and multiply by the quantity
            JessOttman

                 I am entering x 2 and it is a number field.

                 This layout has just been a template to print for many years and I have been converting it into a database for the purpose of pricing.(In excel)

                 So I was asked could we separate out the accessory quantities for pricing and inventory purposes, after the main pricing was done.

                 The layout need not be anything fancy just practical as it will not be edited just viewed.

                  

            • 3. Re: Count portal values and multiply by the quantity
              philmodjunk

                   Why enter the "x"? As long as the field is of type number it won't be a problem but don't see the need for the extra character.

                   See this tutorial on summary reports and see if it helps you out: Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: Count portal values and multiply by the quantity
                JessOttman

                     Its is for the purpose of combining all accessories needed per hose. It was manually entered before in excel and if i have a combination field with

                     11" procap or 2 2" seal it looks messy. Il take a look at the tutorial

                      

                     Thanks

                      

                • 5. Re: Count portal values and multiply by the quantity
                  philmodjunk

                       There are other ways to display "x" where/when needed that does not require the user to enter it into the number field.

                  • 6. Re: Count portal values and multiply by the quantity
                    JessOttman

                         That would be preferable, but how would you do it?

                         Also in the sub summary tutorial report, it adds all quantities. I just want to add the different types of accessories in the found count. 

                         1" A =20

                         1" B= 15

                         1" C= 8

                         -----

                         1.1/2" A= 4

                         1.1/2" B= 20

                         1.1/2" C= 9

                          

                         I may have picked up on the information wrong from the tutorial.

                    • 7. Re: Count portal values and multiply by the quantity
                      philmodjunk

                           In the Tutorial, take a look at how the sub summary layout part works with a summary field. It does not add up all records but all records that make up a group after sorting so you sort your records by Part to get groups of the same part. And towards the end of the tutorial, there's a description of how to drop out the list of individual records and just list the sub totals.

                           

                                That would be preferable, but how would you do it?

                           There is more than one way and the best option for you depends on specific details of how your database is designed. Two possible options:

                           A calculation field can combine the data with the "x" character: Field 1 & " x " & Field 2. And an if function can make that happen selectively if necessary.

                           The character can simply be placed on the layout--possibly between two merge fields: <<Field1>> x <<Field2>>

                      • 8. Re: Count portal values and multiply by the quantity
                        JessOttman

                             This is your advice from my last post " Combine Portal Fields To One" regarding combination fields in the same database as this one.

                        Option 1:

                        Define a calculation field in the portal's table that combines the Qty and description how you want for just that one row.

                        Use the first approach that you tried, Substitute ( List ( RelatedTable::Field )... but refer to this calculation field instead of one or both individual fields.

                             In my qantity field I have 1 x, 2 x etc so that I get: 1 x procap, 2x seals etc. 

                             How can I summarise data in relation to each description? By simple sub summary? Show related records from Line Items or main table?

                              

                              
                        • 9. Re: Count portal values and multiply by the quantity
                          philmodjunk

                               So the field in RelatedTable::Field could be the calculation field that uses Field1 & " x " & Field2 to produce the value to be listed by the list function.

                               In the tutorial, you have the line items for an invoice being used to produce a summary report. Sorting groups the records by type of line item so that a sub summary layout part with a summary field can show totals for that group of records.

                               The same method should work for you as far as I can tell here. You could sort your records by description to get the needed groups, but a field with an Id code in it that identifies each type of part would be a better option if possible.

                          • 10. Re: Count portal values and multiply by the quantity
                            JessOttman

                                 Great I can change that value to contain x now.

                                 Perfect I have a drop down list for product ID which is selected before the description appears. This product ID is set out in to make each product unique. eg.  01 1" Enc seal      02 1" Pro Cap etc 

                                 I'll see if I can adapt this method to my database

                                  

                                 Thanks

                            • 11. Re: Count portal values and multiply by the quantity
                              philmodjunk

                                   If: 01 1" Enc seal

                                   Is your product ID, I recommend that you change methods and use an auto-entered serial number for your product ID.