5 Replies Latest reply on Aug 18, 2016 8:52 AM by Ben_B94

    Count Calculation for Palletizing

    isamudysan

      First, my apologies for forgetting the person who had helped me with this calculation in the past.

       

      I have used the below calculation for palletizing in the c_Pallet field:

       

      Let ( [

          _count = ExecuteSQL ( "SELECT COUNT(*) FROM MainTable" ; "" ; "" ) ;

          _m = Div ( _count ; 180 ) + 1

      ] ; "P" & _m

      )


      What I need today would be a couple of things, if possible:

      1. I need to adapt this calculation for 2 product types, i.e., phones and laptops in the same table.  Phones with a pallet size of 180, and laptops a pallet size of 18.  I know by changing the divisor number will affect the of pallet count number.
      2. I would like to be able to reset the count on a monthly basis, or when a new record is created.

       

      The product type values are stored in the ProductType field which is a calculated field looked up by its respective part number.

       

      What I've tried thus far:

       

      Let ( [

          phone = ExecuteSQL ( "SELECT COUNT(*) FROM MainTable" ; "" ; "" ) ;

          _m = Div ( _count ; 180 ) + 1 ;

          lap = ExecuteSQL ( "SELECT COUNT(*) FROM MainTable" ; "" ; "" ) ;

          _l = Div ( _count ; 18 ) + 1 ;

      ] ;

        Case (

          ProductType = "Phone" ; "P" & _m ;

          ProductType = "Laptop" ; "P" & _l ; ""

        )

      )

       

      Unfortunately, didn't work as intended, LOL.  But, I do know that I am missing something.

       

      Thanks for all your help everyone!

       

      Toufue

        • 1. Re: Count Calculation for Palletizing
          DavidJondreau

          Your current ExecuteSQL() is saying, "Count all the items divide by 180, then add that to all the items divided by 18". You need to count only the phones and only the laptops first. You need WHERE clauses.

           

          BTW, I don't think Div () + 1 is the best choice here. If there are 18 phones, you'll be over by 1. Ceiling() and division is a better choice.

           

          Let ( [

              phone = ExecuteSQL ( "SELECT COUNT(*) FROM MainTable WHERE ProductType='Phone'" ; "" ; "" ) ;

              _m = Ceiling ( phone / 180 ) ;

              lap = ExecuteSQL ( "SELECT COUNT(*) FROM MainTable" WHERE ProductType='Laptop' ; "" ; "" ) ;

              _l = Ceiling ( lap / 18 ) ;

          ] ;

          "Phone: " & _m

          & "¶" &

          "Laptop: " & _l ; ""

            )

          )

          • 2. Re: Count Calculation for Palletizing
            isamudysan

            DavidJondreau, thanks for your quick reply.  I did try the WHERE clause after the above calculation; but, was still using the Div () +1 function.  I'll give this a shot tomorrow when i get in to work.  I'll need to read more about the Ceiling () function tonight.

            • 3. Re: Count Calculation for Palletizing
              isamudysan

              alrighty, so i tried the above calculation with a little twist by adding the + 1 because it was giving a pallet number of 0, and that's not the number i want to begin with:

               

              Let ( [

                  phone = ExecuteSQL ( "SELECT COUNT(*) FROM MainTable WHERE ProductType='Phone'" ; "" ; "" ) ;

                  _m = Ceiling ( mb/4 ) + 1 ;

                  lap = ExecuteSQL ( "SELECT COUNT(*) FROM MainTable WHERE ProductType='Laptop'" ; "" ; "" ) ;

                  _l = Ceiling ( imac/4 ) + 1

              ] ;

                 Case (

                   ProductType = "Macbook" ; "P" & _m ;

                   ProductType = "iMac" ; "P" & _l ; ""

                )

              )

               

              the result was that the first four records calculated correctly with a P1 for phone, as the phones were scanned first.  the calculation problem arises after the fourth record. if i scanned in the laptop's part number, the calculation would start with P2, a second pallet.  that's not exactly what i want.  i want laptops to have their own pallet count beginning with P1.  basically, all the laptops will have their own pallet count, and likewise the phones.

              • 4. Re: Count Calculation for Palletizing
                DavidJondreau

                Based on your description, you will get an inaccurate pallet count if you add +1.

                 

                The calc you are posting in this post is confusing...you're mixing up phone and mb and lap and imac as variables. It wouldn't actually resolve in a calculation dialog. Also, is the ProductType iMac or Macbook or Phone or Laptop? You're mixing them up here.

                 

                I suspect your calculation was failing all along. You were getting P1 for phone because you were getting 0 +1. And now you're getting 1 for laptops, but adding +1.

                • 5. Re: Count Calculation for Palletizing
                  Ben_B94

                  Sorry I can't offer a straight answer but If you get a chance I would definitely check out seedcodes SQL explorer. Its the perfect tool to create an SQL query like you need, albeit you'll have to plug in your databases etc to then begin making queries and I don't know how much time you have but I have found it an immensely useful tool and would definitely recommend it! 

                   

                  http://www.seedcode.com/filemaker-sql-explorer/