Wildcard with If, then statement

[istuart]

Hi all,

I am trying to use a wildcard in an If, then statement. It's not working because the field property is string and perhaps the use of quotation marks is looking for a literal value.

The numbering convention for the invoices are:
vendorID_Month/year services rendered_Month/year paid
so it might look like this: 123456_012007_012007. Any invoice where the month/year service rendered and paid are not the same should be marked as an adjustment. The vendor Id is different for each vendor so I tried to use a wildcard for the vendor ID portion of the invoice ID.

If {NS_INVOICE_ID)= "*_012007_022007", "*012007_032007"=, "*012007_042007",
"*012007_052007", "*012007_062007"] then "Adj" Else {NS_INVOICE_ID}
End If

 

[Turkbear]

Hi,
Try using the Instr and Mid functions to parse {NS_INVOICE_ID)
, not wildcards..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[istuart]

can you elaborate? I'm not familiar with Instr and Mid functions to parse

 

[Turkbear]

Hi,
In the CR help section there will be details, but essentially you want to compare the part of the data in the {NS_INVOICE_ID) field between the first pair of Underscores with the part after the second _ ..
So something like ( please check the help files and especially check on my ( balancing.)

Instr({NS_INVOICE_ID),1,"_")

will give you the position in the string of the FIRST _ character
while

Instr({NS_INVOICE_ID),(Instr({NS_INVOICE_ID),1,"_") +1),Instr({NS_INVOICE_ID),1,"_") )

will give you the position of the second one - the first parameter in the function is the start position for the search, so the first formula starts with the first character while the second formula starts 1 place after the first _ character's position.

Using the Mid function you can parse out those parts for comparison..

Mid({NS_INVOICE_ID),Instr({NS_INVOICE_ID),1,"_"),Instr({NS_INVOICE_ID),(Instr({NS_INVOICE_ID),1,"_") +1),Instr({NS_INVOICE_ID),1,"_") ))

Should return the part between the f1rst 2 _ characters..
while

Mid({NS_INVOICE_ID),Instr({NS_INVOICE_ID),(Instr({NS_INVOICE_ID),1,"_") +1),Instr({NS_INVOICE_ID),1,"_") )
)

Should return the last part ( between the second _ and the end of the string.

Compare those two results to determine if 'Adj' is needed..

This should give you an idea of how to proceed, but please check and test. I cannot test it, so it may need modification..












To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
As I thought more about it, there is an easier way:

Use the Split() function as follows:

StringVar MyArray;
MyArray := Split({NS_INVOICE_ID),'_');

If MyArray(2) = MyArray(3) Then
"Adj." 
Else
{NS_INVOICE_ID)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[istuart]

Thanks for your help. I tried your first suggestion, but couldn't make it work. I ended up using Trim Right which worked for me.

Right({NS_INVOICE_ID}, 13)

Then I used If, then, else argument

If{@Adjustment}= "012007_022007" Then "Adj"
Else If {@Adjustment}="012007_032007" Then "Adj"
Else If {@Adjustment}="012007_042007" Then "Adj"
Else If {@Adjustment}="012007_052007" Then "Adj"
Else If {@Adjustment}="012007_062007" Then "Adj"
Else If {@Adjustment}="022007_032007" Then "Adj"
Else If {@Adjustment}="022007_042007" Then "Adj"
Else If {@Adjustment}="022007_052007" Then "Adj"
Else If {@Adjustment}="022007_062007" Then "Adj"
Else If {@Adjustment}="032007_042007" Then "Adj"
Else If {@Adjustment}="032007_052007" Then "Adj"
Else If {@Adjustment}="032007_062007" Then "Adj"
Else If {@Adjustment}="042007_052007" Then "Adj"
Else If {@Adjustment}="042007_062007" Then "Adj"
Else If {@Adjustment}="052007_062007" Then "Adj"
Else If {@Adjustment}="062007_072007" Then "Adj"
Else "Reg

 

[Turkbear]

Hi,
If you need to compare the second part of the ({NS_INVOICE_ID) field to the third part
( you post said that you needed to determine if the
Month/year services rendered [ the part between the first and second _ chars ] was different from the
Month/year paid [ the part betwen the second _ and the end])

I don't see how your solution would work, unless you know all the possible values in advance.

My second post ( and the one I would try) had an error in the formula.
It should have been:

StringVar MyArray;
MyArray := Split({NS_INVOICE_ID),'_');

If MyArray(2) <> MyArray(3) Then
"Adj." 
Else
"Reg"

That should work for any values in the {NS_INVOICE_ID) field.

Did I miss something in your requirements?






To Paraphrase:"The Help you get is proportional to the Help you give.."