How To Add To Records Values Together? 追蹤

0
Avatar
Legacy Poster

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

6 意見

0
Avatar
Legacy Poster
評論操作 永久連結

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
Avatar
Legacy Poster
評論操作 永久連結

[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
Avatar
Shotaro Ito
評論操作 永久連結

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
Avatar
Legacy Poster
評論操作 永久連結

[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
Avatar
Shotaro Ito
評論操作 永久連結

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
Avatar
Legacy Poster
評論操作 永久連結

[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!

登入寫評論。