Inquiries: Relations - "is in"

How would you use contains, when the fields are on the wrong side though? I thought it wasn’t bi-directional.

I have multiple left-table fields I need to find on a right-table field. The Adjustment Description (TranDesc) contains the Receipt Nbr and it contains the Item ID, but I cannot write:

ReceiptNbr CONTAINS TranDesc
AND
InventoryID CONTAINS TranDesc

TranDesc is coming from the right and I don’t see a way to check if my left-handed values are inside of it. I was hoping that was what “is in” meant, but apparently not. So what tool do we use for this?

​@Michaelh You can use child tables on the left side, you just need to click the drop down and scroll to find them, likewise, you can use parent tables on the right side of the data links.

I also tried pushing this to the TOP of my relationships, changing left join to right join to make it work, and now it just spins until it times out.

The condtion was simple enough:
TRAN DESC contains RECEIPT NUMBER. 

Oh well, I’ll just keep shuffling the pieces around until it sticks. Thanks for the info on “is in”

​@Michaelh  - You have to be really careful with putting search strings against text strings.   There’s a solid chance that your database won’t be indexed in a way that lets that kind of join work fast.

If possible I’d try to trim data further, or make it so the GI can only be run once a Parameter has been supplied that limits the information being checked significantly.

Thanks Will and Benjamin. I was able to avoid Contains (which was slow) by using Starts With, which has a much better runtime. Since I can control the data in the description, I pasted the LineNbr and PONbr right at the front so this would be easier to grab. Now I am using:

CSTR(LineNbr)+CSTR(PONbr) StartsWith Left(TranDesc,Len(CSTR(LineNbr)+CSTR(PONbr)-1)

It’s slow, but not an issue for this task, I just give a minute to execute. The overhead for contains resutls in a timeout.

​@Michaelh  - Reasonable, I’d be really tempted to add an attribute/extra field/something and shove the data into a separate field just for being able to do exact string matches and improve database indexing.

Obviously you know the performance context of you/your customer in more detail than I do though.  Might be something to think of if you hit future slowdowns or if this GI is going to be referenced frequently.

LOLOLOL, it’s funny you should say that. I have asked for permission to add the fields this weekend to the Adjustments Screen when my users are offline.

Side Question: Anyone know if a simple UDF vs an actual field on the screen has performance implications when doing comparisons?