Label report with user defined input -

[Former Member]

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 · Accepted solution]

Create a function, Find_Item, that returns the array index of an item number in {?teim.num}, then use this as the array index into {?qty}. So the function would be something like (basic syntax):

function Find_Item (itemno as number) as number 
  dim i as number
  i = 1
  while (i <= UBound({?item.num}) and ({?item.num}<i> <> itemno)
    i = i + 1
  wend
  if ({?item.num}<i> = itemno) and (UBound({?qty}) >= i) then
    Find_Item = i
  else
    Find_Item = -1  ' Not found, or not enough qty's entered
  end if
end function

Then, for your record selection formula use:

dim idx as number
idx = Find_Item({database.itemnum})
if idx < 0 then
  formula = false
else
  if {numbertable.number} > {?qty}[idx] then
    formula = false
  else
    formula = true
  end if
end if

HTH,

Carl

[Former Member · Accepted solution]

can you elaborate on the selection part. I have the function written but seem to be having issues with the selection side

  if {numbertable.number} > {?qty}[idx] then
   

I receive a error :need - then (it points to right behind the {?qty}(here) for a then - I did plug in my data not sure whats wrong

Please forgive me - I'm new to this.

dim idx as number
idx = Find_Item({inventory.itemnum})
if idx < 0 then
  formula = false
else
  if {table.number} > {?qty}[idx] then
    formula = false
  else
    formula = true
  end if
end if

Edited by: max274 on Apr 1, 2009 10:12 PM

[Former Member · Accepted solution]

1) When do you receive the error?

2) What is the exact error?

3) If it's happening during run time, Crystal designer should show the formula editor, with the current values of variables. What are they?

Carl

[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