13 Replies Latest reply: Sep 5, 2013 7:21 AM by Nayan Lalla

# Items existing in a table

Hi

I have items that appear on more than one price lists.  If a item appears on say, Price List A, it must give me '2' otherwise it must give me '1'.

How do i do the scripting on the front end.

I've tried this formula  if(Price List = 'Price List A', 2,1) , however the result for that item appears on two lines, which i dont want.

kind regards

Nayan

• ###### Re: Items existing in a table

Could you post a small sample app with your expected outcome?

• ###### Re: Items existing in a table

Hi Swuehl

Will do.

Kind regards

Nayan

• ###### Re: Items existing in a table

Hi Swuehl

Below is an extract of the table im working with.  It’s a list of items which appearing in different price lists.

My query is :    If an item appears in Price list A, give me ‘2’ otherwise give me ‘1’.

So, for eg, Item 9 , appears in Price List A, hence the result I want in a is ‘2’.  For Item 8, it does not appear in Price list A, hence the result should be ‘1’.

(please note the values in the columns below are relevant for the scripting I want to achieve).

Hope this helps.

Kind regards

Nayan

PRICE LISTS

Item Code

A

B

C

D

Item 1

59.95

83.95

-

-

Item 2

-

71.95

-

-

Item 3

-

83.95

-

-

Item 4

-

71.95

-

-

Item 5

-

83.95

-

-

Item 6

-

49.95

-

-

Item 7

-

84.95

-

-

Item 8

-

145.95

-

-

Item 9

154.95

139.95

129.95

-

Item 10

119.95

119.95

119.95

119.00

Item 11

189.95

201.11

-

189.95

Item 12

119.95

119.95

-

107.95

Item 13

-

129.95

-

-

Item 14

48.95

48.95

48.95

48.95

Item 15

69.95

79.95

-

-

Item 16

-

79.95

-

-

Item 17

42.95

49.95

-

-

Item 18

18.95

17.95

18.95

18.95

Item 19

-

89.95

-

-

Item 20

107.95

-

-

-

Item 21

94.95

89.95

-

-

Item 22

95.00

-

-

-

Item 23

139.00

-

-

-

Item 24

222.00

-

-

-

Item 25

132.00

139.95

-

-

Item 26

-

189.95

-

-

Item 27

189.00

179.00

-

-

Item 28

420.00

379.00

-

-

Item 29

79.95

-

-

-

Item 30

320.00

-

-

-

Item 31

799.00

-

-

-

Item 32

399.00

-

-

-

Item 33

399.00

-

-

-

Item 34

699.00

-

-

-

• ###### Re: Items existing in a table

I assume your data looks like shown in this inline table.

Then it might be enough to check if there is a number in column A:

if(isnum(A),2,1) as FlagPriceListA

Here is the full script snippet:

Set DecimalSep = '.';

Set ThousandSep = ',';

PRICE_LISTS:

if(isnum(A),2,1) as FlagPriceListA

INLINE [

Item Code,A,B,C,D

Item 1,59.95,83.95,-,-

Item 2,-,71.95,-,-

Item 3,-,83.95,-,-

Item 4,-,71.95,-,-

Item 5,-,83.95,-,-

Item 6,-,49.95,-,-

Item 7,-,84.95,-,-

Item 8,-,145.95,-,-

Item 9,154.95,139.95,129.95,-

Item 10,119.95,119.95,119.95,119.00

Item 11,189.95,201.11,-,189.95

Item 12,119.95,119.95,-,107.95

Item 13,-,129.95,-,-

Item 14,48.95,48.95,48.95,48.95

Item 15,69.95,79.95,-,-

Item 16,-,79.95,-,-

Item 17,42.95,49.95,-,-

];

• ###### Re: Items existing in a table

Hi

Im not sure why the table came out that way.  Thanks for your feedback, I’ll try your formula.

By the way, how do you reply on the website.  There used to be the function to reply.

Just to let you know, the table should look like this.  I’ve taken a screen shot.  (Hope it comes out right)

Kind regards

Nayan

• ###### Re: Items existing in a table

Hi Swuehl

Thanks, it works.  J

Regards

Nayan

• ###### Re: Items existing in a table

Hi Nayan,

try this

if(count(Price List) >1, 2,1)

It will works...

• ###### Re: Items existing in a table

try Match function ,

it will be like this, if(Match(Item, PricelistA) > 1,2,1). I have seen this function work sometimes where condition of equal fails in if statement.

• ###### Re: Items existing in a table

Thank you .  Will try it too.

Kind regards

Nayan

• ###### Re: Items existing in a table

You can also try exist function if you are not comparing row by row

• ###### Re: Items existing in a table

I have not used the exist function before.  How would I apply it.

Kind regards

Nayan

• ###### Re: Items existing in a table

you will apply in your script, basically like

load if(exists(item),2,1) but for that you have to rename pricelist2 field

as item. or you can chose any other name.

It will check if item name you are currently loading exist in previously in

the same field. that , if pricelistA has item value "abc" and you are

loading item "abc" it will make condition true and return 2 else 1.