I have a custom record that is a child of an Invoice. I want to create a search for these custom records that will use the parent invoice fields as criteria and also some of the results.
As you know, Invoice has fields like Amount Paid, Amount Remaining, etc
So I have setup the criteria:
Invoice : Item on Any Line MyItem
Invoice : MainLine = true
This doesn't work, it appears you can't mix ItemOnAnyLine and MainLine - they filter each other out for some reason.
This is really limiting and makes me think I'm doing something wrong. I mean, the strange "MainLine" filter is almost always required when you don't want to get tons of duplicate results but does this mean you also can't search for transaction with certain items on them?
I guess if I think about the SQL I could imagine what may be happening:
They are joining the line rows with the transaction row and the ItemOnAnyLine filter isn't using a sub select but actually depending on the joined line rows. So MainLine = true wipes out the join from the query and also any line-table related criteria?
Is there another way for me to accomplish what I'm after?
Anyone have any ideas?