
1. Re: Count Calculation for Palletizing
DavidJondreau Aug 17, 2016 3:54 PM (in response to isamudysan)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 Aug 17, 2016 4:34 PM (in response to DavidJondreau)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 Aug 18, 2016 8:11 AM (in response to DavidJondreau)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 Aug 18, 2016 8:29 AM (in response to isamudysan)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 Aug 18, 2016 8:52 AM (in response to isamudysan)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!