How To Add To Records Values Together?
Hello,
We are trying to print Master Labels for CAT. What we are doing is pulling the quantity data from our manufacturing database (Vantage) through Packing Slip numbers. Some packing slips have two or more records, and what we'd like to do is print ONE label with the TOTAL quantity.
For example, if packing slip 70192 has two records, and one record shows a quantity of 3, and the second record shows a quantity of 6, we'd like to print one label with the total quantity 9 on it.
Is there a way we can do this with visual basic scripting? I just need a point in the right direction.
Thanks in advance!
Jesh
We are trying to print Master Labels for CAT. What we are doing is pulling the quantity data from our manufacturing database (Vantage) through Packing Slip numbers. Some packing slips have two or more records, and what we'd like to do is print ONE label with the TOTAL quantity.
For example, if packing slip 70192 has two records, and one record shows a quantity of 3, and the second record shows a quantity of 6, we'd like to print one label with the total quantity 9 on it.
Is there a way we can do this with visual basic scripting? I just need a point in the right direction.
Thanks in advance!
Jesh
0
-
How familiar are you with the VB script in BarTender?
For stuff like this, I almost always create "dummy" fields that I place off the printable area of the label. I would pull your two records into two of these unprinted fields, and then run the VB script in a third printable field using sub-string values from the dummy fields.
Edit: Oh, are you talking about pulling data from two different [i]rows[/i] from your database. I'm not sure the amount of success you'll have with that one...0 -
[quote name='nRyder' timestamp='1337699311' post='2488']
How familiar are you with the VB script in BarTender?
For stuff like this, I almost always create "dummy" fields that I place off the printable area of the label. I would pull your two records into two of these unprinted fields, and then run the VB script in a third printable field using sub-string values from the dummy fields.
Edit: Oh, are you talking about pulling data from two different [i]rows[/i] from your database. I'm not sure the amount of success you'll have with that one...
[/quote]
Yes two different rows. Any idea if it's possible?0 -
Shotaro Ito
★ BarTender Hero ★
If you don't have to print each record's data - just want to print common ID and sum of quantity, you can use custom SQL to get sum of quantity.
something like..
[sql]SELECT PACKINGSLIP, SUM(QTY) AS SUMQTY FROM dbo.TABLE1 GROUP BY PACKINGSLIP ORDER BY PACKINGSLIP[/sql]
However if you want to get value from each record separately and print to single label, that would be difficult - that should be done by database side, you need to create a query or table to represent data as single label per record.0 -
[quote name='Shotaro I -Seagull Support' timestamp='1337732853' post='2493']
If you don't have to print each record's data - just want to print common ID and sum of quantity, you can use custom SQL to get sum of quantity.
something like..
[sql]SELECT PACKINGSLIP, SUM(QTY) AS SUMQTY FROM dbo.TABLE1 GROUP BY PACKINGSLIP ORDER BY PACKINGSLIP[/sql]
However if you want to get value from each record separately and print to single label, that would be difficult - that should be done by database side, you need to create a query or table to represent data as single label per record.
[/quote]
Unfortunately we are trying to do the second option. We'd like to print ONE label with the QTY from more then one record on that single label. Is it possible to do in Bartender? We are using PROGRESS database and are not familiar with programming in it.
Thank you for the help so far!0 -
Shotaro Ito
★ BarTender Hero ★
I don't have a good idea of how PROGRESS SQL would be - anyway you can enter custom SQL like above, from BarTender's File > Database setup > SQL tab.
If you connect to the database via ODBC / OLEDB, you might be able to use this option.
Resolve it using BarTender's VB script would be difficult - because, BarTender document can only take single record per label, so BarTender's database connection cannot provide other record's data.0 -
[quote name='Shotaro I -Seagull Support' timestamp='1337825000' post='2500']
I don't have a good idea of how PROGRESS SQL would be - anyway you can enter custom SQL like above, from BarTender's File > Database setup > SQL tab.
If you connect to the database via ODBC / OLEDB, you might be able to use this option.
Resolve it using BarTender's VB script would be difficult - because, BarTender document can only take single record per label, so BarTender's database connection cannot provide other record's data.
[/quote]
Thank you for the help Shotaro. We did get this to work like:
SELECT
SUM("PUB"."ShipDtl"."OurInventoryShipQty") AS SUMQTY,
"PUB"."ShipDtl"."PackNum",
"PUB"."ShipDtl"."PartNum",
"PUB"."ShipDtl"."RevisionNum",
"PUB"."OrderHed"."PONum",
"PUB"."ShipHead"."PackNum"
FROM
"PUB"."ShipHead" , "PUB"."ShipDtl" , "PUB"."OrderHed"
WHERE
"PUB"."ShipDtl"."PackNum" = "PUB"."ShipHead"."PackNum" AND "PUB"."OrderHed"."OrderNum" = "PUB"."ShipDtl"."OrderNum" AND ("PUB"."ShipHead"."PackNum" = ?Packing Slip #)
GROUP by
"PUB"."ShipDtl"."PackNum",
"PUB"."ShipDtl"."PartNum",
"PUB"."ShipDtl"."RevisionNum",
"PUB"."OrderHed"."PONum",
"PUB"."ShipHead"."PackNum"
ORDER by
"PUB"."ShipDtl"."PackNum",
"PUB"."ShipDtl"."PartNum",
"PUB"."ShipDtl"."RevisionNum",
"PUB"."OrderHed"."PONum",
"PUB"."ShipHead"."PackNum"
Thank you!0
請登入寫評論。
評論
6 條評論