Selection Criteria Formula that references database fields

[0sprey]

I am trying select records where only the following condition exists:

The second, third and fourth characters of the ‘ModelNo’ are not found anywhere within the ‘PartNo’.
(Both ‘ModelNo’ and ‘PartNo’ are fields found within each record of the database table used by the report. There is one only database table used by this report.)


I first created a formula called ‘@Model_234’, where:

@Model_234 = (Mid({tbl_ ModelNo},2,3))

I then set up the Selection Criteria using the following formula:

not ({tbl_PartNo} like [*{@Model234}*])


However, this Selection Formula does not filter the records at all.

Any thoughts on how to accomplish this task would be appreciated.

 

[lbass]

Please show a sample of part numbers and model numbers as they would appear in a set of data. Then show what you would expect for results. It is unclear whether you want to eliminate part numbers that contain any of a set of partial model numbers or whether you just want to eliminate rows where the partial model number appears in the part number. For example:

Data set:
formula result part number

abc 12abc456
abc 345def78
def 9234xyz1
jkl 17mno94

Would you expect the result set to be (result 1):

abc 345def78
def 9234xyz1
jkl 17mno94

Or, only (result 2):

jkl 17mno94

In other words, do you want to eliminate all part numbers that contain ANY of the model number formula results, i.e., search all part numbers for the presence of ANY partial model number? Or do you just want to eliminate those that appear in the same row?

-LB

 

[0sprey]

I only wish to select records where :

Upon examination of each record in succession...

Select only if : the set of characters in position 2 3 4 of the 'ModelNo' field ( i.e. 550) is not found anywhere within the 'PartNo' field of that same record.


Record1 : ModelNo - C5503445548 PartNo - xa33234qA45rt6 550 56ty [ Skip ]
Record2 : ModelNo - D5506858437 PartNo - 25w434e3t22222243 555rt [Select]
Record3 : ModelNo - E6578934455 PartNo - 4ewr134w56465745 6tyutt [Skip]

 

[lbass]

Change your formula to:

Not({tbl_partno} like '*'+{@Model234}+'*')

-LB

 

[0sprey]

With the NOT function I get no results and removing the NOT function I get all records. So no luck yet.

I tried pasting the formula into Notepad and replacing the 's with "s to see it that would work but same results.
(Are these intended to be single quotes ? I have not seem used single quotes used in this way in CR... so was not sure)

I added {@Model234} to the Details Section and it is generating the correct values.


(What are the "+" symbols designed to do in CR ? I have not used these before.)

Thanks Ospery

 

[lbass]

Please post exactly what you entered in the selection formula.

Single or double quotes don't matter in the selection expert. The "+" is like an "&" but is specific to strings. Use whichever you like. In a command or in the SQL expression editor it would matter, but not in the selection expert.

-LB

 

[pmax9999]

I don't see any reason why the solution offered by lbass would not have worked.

However, out of curiosity, I thought I would try it with a different approach and achieved the desired result with the following Selection Formula (you will need to substitute your table/column names):

[Code @Record_Selection]
INSTR({Table.PartNo}, MID({Table.ModelNo}, 2, 3)) = 0
[/Code]

Hope this helps.

Regards
Pete

 

[0sprey]

I ran Pete's formula and 4 records were selected. I checked this and found that there was a Text Case issue so I used the Uppercase function and that had the formula retuning zero records.


I then re-examined the data in the PartNo. and found that there was information within that contained part of the Modoel No. in all cases (this is a large data field) so that as was reason that both formulae were coming back with zero records. Both formulae were working correctly .... my oversight of the data field contents was the real issue.

I have found another field that seems like it will work to filter the data as needed.... so far it is working correctly.

Thanks you both for your input.... you helped me located the real issue and things are ok now. ! Merci !