Best way to find descriptions in BAQ

ERP 10
, ,
1

Good Morning,

Anyone know the best way to join and display a description instead of a value in the CheckHed.Checksrc field?

Was looking for a source table to join to but could not find one. Maybe there is a document that shows translate values I could reference and related records?

Thanks!
Pete

image
image844Ă—59 2.62 KB

2

Not sure if it’s “best way” but I personally would add a calculated field along these lines:

(case
when CheckHed.Checksrc = 1 then “AP Disbursements”
when CheckHed.Checksrc = 2 then “AP Manual”
when CheckHed.Checksrc = 3 then “AP User”
when CheckHed.Checksrc = 4 then “PR Manual”
when CheckHed.Checksrc = 4 then “PR User”
else CheckHed.Checksrc
end)

You could even name them anything that makes more sense to you and your users.

2 Likes
3

Thank you - Thats a great idea!

Any idea why I may get this error in the syntax? I haven’t used the case statement before

Your help is appreciated!

Pete

image
image754Ă—327 20.8 KB

4

The error is because the text you copied has “Fancy Quotes” instead of "Normal Quotes".

@lgraham should have used the formatting commands, making her post:

(case
when CheckHed.Checksrc = 1 then "AP Disbursements"
when CheckHed.Checksrc = 2 then "AP Manual"
when CheckHed.Checksrc = 3 then "AP User"
when CheckHed.Checksrc = 4 then "PR Manual"
when CheckHed.Checksrc = 4 then "PR User"
else CheckHed.Checksrc
end)
5

Thanks for helping Calvin

I seem to be getting that same error no matter what quotes I use. also tried single quotes. Any other thoughts?

Regards
Pete

6

What is the name of your calc field, and the type?

EDIT

Those need to be single quotes around the strings, like:

(case
when CheckHed.Checksrc = 1 then 'AP Disbursements'
when CheckHed.Checksrc = 2 then 'AP Manual'
when CheckHed.Checksrc = 3 then 'AP User'
when CheckHed.Checksrc = 4 then 'PR Manual'
when CheckHed.Checksrc = 4 then 'PR User'
else CheckHed.Checksrc
end)
1 Like
7

It definitely would be the single quote '

Try removing the () around the case statement. It isn’t needed.

Also, what data type is your calculated field? If it’s NVARCHAR, then the else may need to be

else ''
1 Like
8

I’d leave those outer parenthesis.

9

Thanks - that seems to have done the trick:

image
image878Ă—531 18.5 KB

10

Hey @littlesistersba - I noticed the Case statement doesn’t actually give you the descriptors from the data dictionary.

  • Case 4 is in there twice (no Case 5)
  • Nothing for Case 6

image
image844Ă—59 2.62 KB

  • Case 4 should be, 'PR'
  • Case 5 should be, 'PR Manual'
  • Case 6 should be, 'PR User'
11

good catch -Thanks again Calvin

12

Slightly shorter:

case CheckHed.Checksrc
when 1 then 'AP Disbursements'
when 2 then 'AP Manual'
when 3 then 'AP User'
when 4 then 'PR'
when 5 then 'PR Manual'
when 6 then 'PR User'
else CheckHed.Checksrc
end
3 Likes