VBScript to sum values available from CSV Database connection
Hi All,
Im currently working on a template where I have a database connection made available via a CSV file. Within this template i have up to 6 lines that hold certain quantities.
The VB Script i'm creating in a new empty object is to basically sum all the quantities available in the field and print a total.
So for e.g - if i have a quantity in line 1 and 2, then it picks those two quantities and sums them, If i have a quantity in line 1, 2 and 3 then it needs to sum the qty in all three lines.
Ive currently written the following VBScript "OnNewRecord" to check and calculate but upon executing it gives no result. I'm sure the script is correct but some parameters maybe wrong. This is the script:-
if ReferenceField("PUM_3001.PUM_3001.PACKQTY [2]") = "0" then value = ReferenceField("PUM_3001.PUM_3001.PACKQTY")
if ReferenceField("PUM_3001.PUM_3001.PACKQTY [2]") > "0" then value = ReferenceField("PUM_3001.PUM_3001.PACKQTY") + ReferenceField("PUM_3001.PUM_3001.PACKQTY [2]")
if ReferenceField("PUM_3001.PUM_3001.PACKQTY [3]") > "0" then value = ReferenceField("PUM_3001.PUM_3001.PACKQTY") + ReferenceField("PUM_3001.PUM_3001.PACKQTY [2]") + ReferenceField("PUM_3001.PUM_3001.PACKQTY [3]")
Please advise.
Thank you.
-
Can you upload a sample of the csv file?
Regards.
0 -
Hi Adam,
I dont have an option to upload the CSV File, ive added a text snippet from the csv:-
STREET,COUNTRYCD,ZIPCODE,CITY,CONTACT,CUSTOMERNUMBER,ORDERNO,DNN,Material,COO,DESCRIPTION,Size,PACKAGENO,SEQUENCEDPACKAGENUMBER,TOTALPACKAGESCREATED,PACKQTY,BARCODECONCAT
Logistics Park New Alkh,SA,14351,Riyadh KSA,+971 50 123 4567,0001313836,1380961975,000000002,84710832,VN,TEST ITEM,S,3011,1,3,12,1380961975123456789012312
Logistics Park New Alkh,SA,14351,Riyadh KSA,+971 50 123 4567,0001313836,1380961975,000000002,84710832,VN,TEST ITEM,XL,3011,1,3,4,138096197509876543210984The second last column - PACKQTY is the what im trying to sum in a different field.
0 -
Are you trying to sum the PACKQTY for all records in the CSV file?
If that's the case it will be a lot more complicated than just printing the value of each record,
you will need to parse the whole file first all the way to the end of the file while saving each record's value
into a memory value using a For Next loop, but vbScript is not a full programming language.
that it's best to to use a report generator.
I have tried to do something similar with no luck,
maybe because my knowledge of vbScript is fairly limited to a one record at the time results.
but if you can get somenone with more experience find a soluciion.
Please make sure you share the solution with all of us to benefit from this knowledge..
Regards.
0 -
Hello Anmol
You need to plug in your CSV file and in the records per item tab set how many records you want to load to print one label.
Later you create a script in the object and add the fields you want to sum. If it is "0" nothing happens. Your first line in the csv file are the column names.
Below you have pictures of how I did it.
value1=int(Field("test.test.PACKQTY"))
value2=int(Field("test.test.PACKQTY [2]"))
value=(value1+value2)0 -
Hi Pawel,
Thank you for the response, that works almost as expected! :-) There is one more element that I need your support with.
My original request is to conditionally sum the database values if available. With the script you've provided, I'm able to sum the values but if a certain record does not exist it does not calculate anything.
The business case is that the label can hold a maximum of 6 lines (6 CSV records), so if the CSV file only contains e.g- 11 records, the first 6 records will be added to the first label instance, calculated and printed. The next 5 records would go onto the next label instance and only the those 5 available lines need to be calculated. It would need to work dynamically if the pending lines to print range from 1 - 5.
I have tried performing some "elseif" functions to conditionally add the database records but it returns nothing. What would the appropriate call be to conditionally calculate ?
Thanks.
0 -
Hi Paweł Gancarz
I've modified the script further to introduce conditional addition of fields:-
value1 = int(Format.Objects("Text 17").Value)
value2 = int(Format.Objects("Text 22").Value)
value3 = int(Format.Objects("Text 32").Value)
value4 = int(Format.Objects("Text 27").Value)
value5 = int(Format.Objects("Text 42").Value)
value6 = int(Format.Objects("Text 37").Value)
if Format.Objects("Text 22").Value = "0" then
value = (value1+value2)
elseif Format.Objects("Text 32").Value = "0" then
value = (value1+value2+value3)
elseif Format.Objects("Text 27").Value = "0" then
value = (value1+value2+value4)
elseif Format.Objects("Text 42").Value = "0" then
value = (value1+value2+value3+value4)
elseif Format.Objects("Text 37").Value = "0" then
value = (value1+value2+value3+value4+value5)
elseif Format.Objects("Text 37").Value > "0" then
value = (value1+value2+value3+value4+value5+value6)
end ifWhat ive done additionally is that a vbscript has been introduced to each qty record on template where it checks if the value of the database field = "" then the value = 0. So this way im able to fill in all the fields with some value.
Next, within the script above im referecing the "Value" of the template objects to pick up the values printed.
Within the script editor, I receive no error when testing the script but upon print, I receive the warning message #3913 - Script error occured in object.. type mismatch : '[String: ""]'
What could be causing this ?
Regards.
0 -
Hello Anmol
I just got the error too and the same thing is happening to me.
There needs to be a variable name that we need to subtract from the number of records.0 -
I noticed that you are testing the value of your objects to see if it equals "0"
But if you enclose the 0 with Double Quotes, then 0 is no longer a 0 (zero) but the character
0 (Character zero, it becomes a String representing the character 0)
Try changing all "0" for Plain 0 (zero value)
Regards
if Format.Objects("Text 22").Value = "0" then
value = (value1+value2)
elseif Format.Objects("Text 32").Value = "0" then
value = (value1+value2+value3)
elseif Format.Objects("Text 27").Value = 0 then
value = (value1+value2+value4)
elseif Format.Objects("Text 42").Value = 0 then
value = (value1+value2+value3+value4)
elseif Format.Objects("Text 37").Value = 0 then
value = (value1+value2+value3+value4+value5)
elseif Format.Objects("Text 37").Value > 0 then
value = (value1+value2+value3+value4+value5+value6)
end if0 -
Hello Anmol
With the help of a friend we were able to figure it out.
You need to add a condition to each object that if it is empty then insert 0.
Below you have the main script and what you have to put in the objects.
value1=(Format.Objects("v1").Value)*1
value2=(Format.Objects("v12").Value)*1
value3=(Format.Objects("v13").Value)*1
value4=(Format.Objects("v14").Value)*1
value5=(Format.Objects("v15").Value)*1
value6=(Format.Objects("v16").Value)*1value=value1+value2+value3+value4+value5+value6
If value="" then
value=0
end if0 -
**SOLVED**
Hi Paweł Gancarz / Jose Adam Flores Sr.,
I have managed to get the calculation working via a slightly different script. What I was missing in the original script was declaring the total qty field itself. So, I've declared the total qty field as "value" and the sum for "Value" = (value1+... +value6).
value1 = int(Format.Objects("Text 17").Value)
value2 = int(Format.Objects("Text 22").Value)
value3 = int(Format.Objects("Text 32").Value)
value4 = int(Format.Objects("Text 27").Value)
value5 = int(Format.Objects("Text 42").Value)
value6 = int(Format.Objects("Text 37").Value)
value = Format.Objects("Text 7").Value
value = (value1+value2+value3+value4+value5+value6)Each field has also been given a vbscript function as described in the previous comment. So each field will always have a value for the vbscript to sum and we can skip using the if else statements altogether.
One last topic pending here is that upon exiting the VBscript for "Text7" i receive the error 5901. However, upon printing in print preview or a physical print, it prints without any issues. This is a minor inconvenience when editing the template (if required) but nothing that affects the end to end operational flow.
Thanks for your support.
0
Please sign in to leave a comment.
Comments
10 comments