Stuck on Custom Query for Dynamic Choices

BLOMASKY

I am sure one of you smarter (and better looking folks) have probably done this before and can do this with one hand behind your back.

3 Tables (err, I mean BOs)

1). UMCodes has universe of valid conversion codes (about 30), They are in an attribute named UM and have things like BOX, PCS, etc.

2). Items is Inventory Item Table that owns MANY ItemConversions. In the form for editing Items, I have a table with the Item Conversions

3). ItemConversions is OWNED BY Items and has an attribute UM.

When I am adding new records to the ItemConversions BO, I want the combo box for the UM to only show UMCodes.UM that are NOT already assigned to this Item. I am getting hung up on the Custom Dynamic Query.
I thought it should look something like:

FIND UMCode WHERE NOT(EXISTS ItemConversions WHERE (ItemConversions IN Item.om_ItemConversions))

But this just returns all of the records.

Thanks
Bruce

tford

Hi Bruce,

Not sure if this will help, but you might want to take a look:

https://forum.awareim.com/d/5274>

BLOMASKY

Thanks for the response Tom. After MUCH Caffeene, I finally got it going.

1st, I had to GROK the AwareIM way of doing things. While I figured I just needed to have a plain_text field and figured the dynamic query would do the work. Was not to be. Instead, I added a relationship, (field now called ps_UMCode), Then on the form, I selected Combo box for this column and my CUSTOM query was:

FIND UMCode WHERE NOT(EXISTS ItemConversion WHERE (ItemConversion.ps_UMCode=UMCode))

and, that worked.
Yea!

Bruce

tford

Hoping that some day there will be an option like this one in Multi-select Drop Downs to be used in situations like yours. It sure would be nice to simply check a box like this one:

BLOMASKY

lets vote on this Tom. All in favor say AYE! ok, passed

bruce

crisg0112

BLOMASKY wrote
lets vote on this Tom. All in favor say AYE! ok, passed

bruce

AYE!