In RDD, adding Criteria to UD Table returns "The field is no valid" error

Hello All. I’m hoping someone has found a way around an apparent limitation in Report Data Definition when adding a UD table AND criteria against that table.

I’ve added UD03 and a relationship to the Part table (Part.Company = UD03.Company). Obviously, a very wide open join. Now it gets interesting as I have to use criteria on the UD03 table to limit what is returned.

I’m adding the following criteria to the UD03 table in order to specify the records I want returned.

Translation: UD03.SI_color_code_c = Part.SI_Color_Side_1_c

So this equation has UD fields on both sides and generates the following error:

This error appears to be related SOLELY to the right side of the equation. If I change change the Part.SI_Color_Side_1_c to Part.PartNum (and ICE to ERP), there is no error.

Now, you might ask, why not just specify that criteria in the RDD’s relationship record. I’m glad you asked… The above criteria will be a “This OR that” criteria - which I cannot accomplish in an RDD relationship. So I have to figure out how to limit what’s returned from that UD table via the table criteria.

Any thoughts will be greatly appreciated.

Did you include (uncheck Exclude) the fields you want to use in the criteria?

Thanks Hugo. I sure did.
In fact, as i mentioned, if I specify this relationship AS a relationship, no problem!!!
It’s just when I use it in the Table Criteria where I get an error. It seems not to like a UD field to the right of the equal sign.

Are UD Fields on ERP Tables using the ICE System code? I’m not on Kinetic yet, but I’ve never seen these used with ICE. Please disregard if that’s a change in Kinetic that I’m not aware of.

I believe so @kve .
Regardless of selection (ICE/ERP) I receive an error with a UD field on the right side of the equal sign, However, if I specify Part.PartNum, I MUST select ERP, otherwise I receive an error (the SAME error).

Step back a step. I’d like an answer too, but what is this report, can you do this in a BAQ instead?

Might be a workaround if this can’t be accomplished.

Thanks @klincecum , I may very well have to create a BAQ report, BUT this is a duplicate of the Job Traveler. There’s a lot of ‘good stuff’ in there I’d like not to have to build from scratch, so I’m using the (copied) RDD, and simply adding a UD table. It’s not a difficult thing to do, unless the join is complicated, which this one is, hence I have to ‘finesse’ the where clause in the Table Criteria.

Shot in the dark.

Add the UD table, AND it’s associated UD Fields table, and link those together, then use the ud fields table as the criteria.

IE, UD03 and UD03_UD, (I forget how they are linked), and then use the UD03_UD.yourfield

or in your case maybe the Part_UD table linked to Part and use Part_UD.yourfield

You shouldn’t normally have to do any of that, but maybe it’ll work ? :thinking:

Link them on Part.SysRowID and Part_UD.ForeignSysRowID

It’s a good thought.
I tried adding Part_UD but the relationship kept failing. It seems like the RDD doesn’t like the “_UD” tables. Again, its a pretty standard add (SysRowId = ForeignSysRowId), but RDD keeps complaining that it can’t find the field ForeignSysRowID.

I could try the other side of the equation (the UD03 table to UD03_UD), but its essentially the same as what I tried.

It seems like, since the UD fields ARE available (without adding the “_UD” tables), adding them generates the error Can’t find the field ‘ForeignSysRowID’.

I’ve found that after you remove the exclusions and save, sometimes you need to refresh the screen before you can get the relationships section to work properly.

I just set it up in mine, and that’s what I had to do.

And you were able to add Part_UD ??


Hmm… I know I’ve gone far down this rabbit hole, but let me investigate further.

Could you consider running the report Without the UD Criteria? Then apply the UD Color join in the SSRS Select Query?


Hi @DaveOlender . In fact, that’s exactly what i am doing in order to test the usability of the report. It’s slow, however, as its pulling down about 5K rows from that UD table - instead of 2!!!

@klincecum, I added the UD03_UD and the Part_UD to the RDD. I established relationships between these by SysRowID = ForeignSysRowID as you outlined. I then selected the UD03 table and created the Table Criteria to limit the returned rows.
No problem with these (Yay!)

Attempting to preview the report, however, reveals the errors. In System Monitor, under History/Logs - unable to bind the UD fields, and no report is produced.

So incredibly frustrating!