Label report with user defined input -

[#1]

I am trying to make a report that will print out the data onto avery labels 5163(which is 2 across u2013 5down =total 10 labels per sheet) with my information. The program already sets your boundaries for the labels so thatu2019s not a issue.

My current select statement works somewhat : if I enter 1 inventory.itemnum and whatever qty I want it prints fine. However when I enter multiple inventory.itemnums it prints each one of those itemnums at whatever qty I enter in the second parameter. So say I enter 2 itemnums and enter qty 2 to print- I get 2 labels of each item num. Currently I this format I have the second parameter set to NOT allow multiple fields u2013 however when I change this to allow my report fails u2013 please see my info and layout stuff below

Here is what I have:

INVENTORY.ITEMNUM} = {?Control Id:} and

{Sheet1_.Column1} <= {?number of labels}

Inventory.itemnum u2013field from database

{?Control Id:}---parameter 1

{?number of labels}---parameter 2

{Sheet1_.Column1}----excel table brought in as secondary database to serve as count feature for report u2013 currently set at 20 but can change from 20 to 10 or higher- whatever qty I want. The most labels I will need for any 1 item is 24 u2013 but I can print 2 sheets of 10 then 1 of 4 if we reduce it to qty 10 in the table.

I have 2 parameters that prompt for data

Parameter 1: it prompts for .itemnum (I have this parameter set to allow multiple inputs of inventory.itemnum)

Parameter 2: it prompts for qty of labels needed. currently not set to allow multiple numbers as I get a failure when I change my parameter to this.

As my info above sits I can enter 1 random inventory.itemnum (parameter 1) and a qty (parameter 2)(1-20 u2013 which is set by a table I built in excel 1-20 u2013 above which is sheet1_.column1)

What I want to be able to do is

1. (Parameter 1 here)Enter inventory.itemnums from random qtyu2019s of 1-10 at any given input time from user prompt in parameter

2. (parameter 2 here)Print label of qtys ranging from 1 label per each item to as many as 10 labels per item

a. For example: I enter

b. Parameter 1:

c. Xitem1.itemnum

d. Xitem2.itemnum

e. Xitem3.itemnum

f. Parameter 2:

g. Qty 1- is the amount of labels needed for xitem1(from parameter 1)

h. Qty 3- is the amount of labels needed for xitem2(u201C u201D)

i. Qty 6- is the amount of labels needed for xitem3(u201C u201C)

3. I need the qtys in parameter 2(labels to print) to work with the inventory.itemnums as they are entered in parameter 1.

I have tried using arrays but they force me to use the same amount of data that I have clauses. Which does not work because I have varying amounts of labels needed at different time-If I have 2 labels and have more clauses than this the report fails. I will not always have 2 or 3 labels to make maybe more maybe less.

Here is a example of what I was trying with arrays:

{field.xitem}=(?item.num)[1] and

{?qty}[1]>={numbertable.number})or

{field.xitem}=(?item.num)[2] and

{?qty}[2]>={numbertable.number})or etc.,etcu2026u2026

This would force me to input as many item.itemnums and qty labels as there are arrays or the report would fail.

Edited by: max274 on Mar 30, 2009 3:11 PM

[Former Member]

max40385

SELECT "INVENTORY"."ITEMNUM", "INVENTORY"."ORDERUNIT",

"INVENTORY"."BINNUM", "INVENTORY"."MODELNUM",

"ITEM"."DESCRIPTION", "POLINE"."ITEMNUM", "POLINE"."ENTERDATE",

"POLINE"."PONUM", "POLINE"."ORDERQTY"

FROM "MAXIMO"."INVENTORY" "INVENTORY",

"MAXIMO"."POLINE" "POLINE", "MAXIMO"."ITEM" "ITEM"

WHERE ("INVENTORY"."ITEMNUM"="POLINE"."ITEMNUM") AND ("POLINE"."ITEMNUM"="ITEM"."ITEMNUM") AND

"POLINE"."PONUM"='24142' AND "POLINE"."ITEMNUM"='1201020021N'

EXTERNAL JOIN POLINE.ORDERQTY>={?C:\Documents and Settings\kpowell\My Documents\test table.xls: Sheet1_.Column1}

*****************my counting table here********

C:\Documents and Settings\kpowell\My Documents\test table.xls

SELECT `Sheet1_`.`Column1`

FROM `Sheet1$` `Sheet1_`

WHERE `Sheet1_`.`Column1`<={?max40385: POLINE.ORDERQTY}

Here is my select statement

POLINE.PONUM} > "23000" and

{POLINE.PONUM}={?My Parameter} and

{POLINE.ITEMNUM}={?Control ID}AND IF

{POLINE.ITEMNUM} in ["1107011010N", "1201020021N"]THEN

{?Control ID}*({@qtY})

my label printing works except when I my inventory item conversion is like the exmaple below - this is trying to get my label program to work

but i want to add some execeptions to my statement currently the

link is as a leftouter join to my counting table which give me my qty of labels to print per user input {?control id} however - some of the items i order with different order units and conversion qtys for example

qty 2 - box-10 - going into inventory 20 - I need 20 labels printed vs just 2 - since the labels to print are directly tied to the requested order qty - i need to add the exceptions because some I order with conversions that I will not convert and I just want to add them list "one of" in the selection formula