Lee Ingalls
asked on
Crystal Reports Record Select using Starts With Parameter
Is it possible to have a Record Select using a "Starts With" pattern from a parameter prompt?
My Order Numbers have multiple Job Numbers associated, example:
OrderNo: 110954
JobNo: 110954-01, 110954-02, 110954-03, 110954-04... 110954-25
The "-" dash numbers represent subassemblies related to the OrderNo and master job "-01
The range of job numbers can vary so it may be easier to prompt for OrderNo and select all relative JobNo, rather than a prompting for a range of JobNo.
My Order Numbers have multiple Job Numbers associated, example:
OrderNo: 110954
JobNo: 110954-01, 110954-02, 110954-03, 110954-04... 110954-25
The "-" dash numbers represent subassemblies related to the OrderNo and master job "-01
The range of job numbers can vary so it may be easier to prompt for OrderNo and select all relative JobNo, rather than a prompting for a range of JobNo.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Creating a view would allow you to convert the Job Number to drop the last several characters. It could then be compared directly to the order number parameter. You could also link the tables to gether.
mlmcc
mlmcc
Lee,
You seem to be suggesting that using the Left function, instead of startsWith, would give you "more flexibility". I don't really see that (they basically do the same thing), but maybe that's not what you meant.
I'm also not sure how either of them would affect whether you use a Left Outer Join or an Inner Join, but I guess that might depend on why you're using a Left Outer Join.
James
You seem to be suggesting that using the Left function, instead of startsWith, would give you "more flexibility". I don't really see that (they basically do the same thing), but maybe that's not what you meant.
I'm also not sure how either of them would affect whether you use a Left Outer Join or an Inner Join, but I guess that might depend on why you're using a Left Outer Join.
James
ASKER
mlmcc, thank you; it worked as described.
James0628 I appreciate the feedback... I was mis-speaking my thought on mlmcc's use of the Left({JobNumberField},6) = {?OrderNo} in teh select forumula and interrepting that as being an alternative to my setting the additional table linkage specifically to LEFT OUTER join.
James0628 I appreciate the feedback... I was mis-speaking my thought on mlmcc's use of the Left({JobNumberField},6) = {?OrderNo} in teh select forumula and interrepting that as being an alternative to my setting the additional table linkage specifically to LEFT OUTER join.
OK. I was just confused, and was trying to make sure that there wasn't more going on than we realized.
James
James
ASKER
The Data Source is MS SQL Server. We've recently changed how we process Orders/Jobs through our ERP -- from Job to Order specific. The canned reports prompt for JobNo and I'm attempting to re-purpose them to OrderNo prompts to include all related JobNo's.
I'm sure I could create some SQL VIEWS via the SQL Mgt Studio. I use queries for a number of Excel solutions. The others I hadn't considered; but I'm a willing student.
BudELee