BAQ Calculated fields to find specific job num

Kinetic ERP
1

Hello~!

I am making a BAQ and have hit a problem wall. I am trying to create a column that shows me all the JobNums that end in “-1-1”. So for example, a job might be 1089628-1-1. I want to see ALL of them. But we have jobs that don’t have that suffix, so I want to avoid seeing those.

Additionally, for the second portion, I want to also see jobs that start with the letter ‘C’.

So one column shows me “-1-1” jobs and the other column shows me jobs that start with ‘C’ like C67981.

Any help would be appreciated. AI is not being very helpful.

Thank you!

2

What do you mean by creating two columns with job numbers? Are you trying to aggregate all these job numbers into a single row? What’s the actual goal?

3

Conditionally checking a value in a “calculated” field would generally use CASE. CASE (Transact-SQL) - SQL Server | Microsoft Learn

Conditionally matching a portion of a text field would generally use LIKE. LIKE (Transact-SQL) - SQL Server | Microsoft Learn

For example,

case when JobHead.JobNum like '%-1-1'
    then JobHead.JobNum
    else null
end
2 Likes
4

You’re talking about calculated fields and not selection criteria, correct?

You’d need to create calculated fields for the two columns and both would need case statements. Something along these lines:

case
when JobNum like ‘%-1-1’ then JobNum
else ‘’
end

5

There other way to do this if it’s just a filter you are after is:

I’d be inclined to add two parameters
Starting with and Ending with

Then put a criteria on the job number (twice) one for each parameter using a begins operation for the starting with and a contains for the ending with.

Try to limit likes as they can be a bit of a performance dog. Not sure if contains and starting are any better to be honest.

6

Assuming all jobs that end with -1-1 are make direct, why not just use the JobProd table to filter based on the demand link?

1 Like