Custom Query in a Drop Down attribute does not pass context

ddumas

I have a Project Object that has ProjectVendors as an attribute (multiple allowed)

I have enabled the "Add New" in the table for ProjectVendors, which displays in the Project Form.

I wish to constrain the Vendor choices to a filtered list of vendors that share the same project type as the project in context.

So, in the form for Entering a new ProjectVendor, I use a custom query, as follows:

FIND Vendor WHERE Vendor IN ProjectTypeVendor.Vendor AND ProjectTypeVendor.ProjectType=Project.ProjectType

What should be in context is Project, as the "Add New" was initiated from the ProjectVendor Table of items in the Project Form.

Since Project.ProjectType exists, the custom query should only return the filtered Vendors .

No Vendors show up in the list. In "SQL terms", this is just a correlated subquery - very easy to code in SQL.

Why is the custom FIND query not returning any Vendors?

Dave

Rennur

Hi ddumas,

I have enabled the "Add New" in the table for ProjectVendors, which displays in the Project Form.

Are you creating a new ProjectVendors object "Add New" or you wish to add existing ProjectVendors to the Project object "Add"?

I wish to constrain the Vendor choices to a filtered list of vendors that share the same project type as the project in context.

So, in the form for Entering a new ProjectVendor, I use a custom query, as follows:

FIND Vendor WHERE Vendor IN ProjectTypeVendor.Vendor AND ProjectTypeVendor.ProjectType=Project.ProjectType

Make sure the ProjectVedors.Project has multiple allowed.

FIND Vendor WHERE Vendor.Project.ProjectType=ThisProject.ProjectType

Cheers

ddumas

Rennur wrote
Hi ddumas,

I have enabled the "Add New" in the table for ProjectVendors, which displays in the Project Form.

Are you creating a new ProjectVendors object "Add New" or you wish to add existing ProjectVendors to the Project object "Add"?

I wish to constrain the Vendor choices to a filtered list of vendors that share the same project type as the project in context.

So, in the form for Entering a new ProjectVendor, I use a custom query, as follows:

FIND Vendor WHERE Vendor IN ProjectTypeVendor.Vendor AND ProjectTypeVendor.ProjectType=Project.ProjectType

Make sure the ProjectVedors.Project has multiple allowed.

FIND Vendor WHERE Vendor.Project.ProjectType=ThisProject.ProjectType

Cheers

ddumas

ProjectVendors is an attribute of Project, and is owned by Project, not the other way around as your FIND suggests.

There are multiple ProjectVendors for a Project. ProjectVendors is a table of items in the Project Main Form. It has the Add New checked. So, since it is on the project form (Project already exists), when I click Add New from the Table of items, I expect the Project to already be in context. Therefore, I expect the Drop down for Vendor in the ProjectVendor entry form to be filtered based on the logic in my FIND.

Looks like AWAREIM has issues with handling what in SQL would be a correlated subquery 🙁

This should be simple - a very common thing to do.

Dave

ddumas

ddumas wrote
Rennur wrote
Hi ddumas,

I have enabled the "Add New" in the table for ProjectVendors, which displays in the Project Form.

Are you creating a new ProjectVendors object "Add New" or you wish to add existing ProjectVendors to the Project object "Add"?

I wish to constrain the Vendor choices to a filtered list of vendors that share the same project type as the project in context.

So, in the form for Entering a new ProjectVendor, I use a custom query, as follows:

FIND Vendor WHERE Vendor IN ProjectTypeVendor.Vendor AND ProjectTypeVendor.ProjectType=Project.ProjectType

Make sure the ProjectVedors.Project has multiple allowed.

FIND Vendor WHERE Vendor.Project.ProjectType=ThisProject.ProjectType

Cheers

ProjectVendors is an attribute of Project, and is owned by Project, not the other way around as your FIND suggests.

Looks like AWAREIM has issues with handling what in SQL would be a correlated subquery 🙁

This should be simple - a very common thing to do.

Dave

Rennur

The custom query filter only filters the ProjectVendors already created and assigned to the Project. You probably do not need a custom query filter in a Parent Child relationship since the Project will only display the Vendors anyway.

When creating new vendors using Add New, you are creating a new child object which will be linked to the parent (Project) automatically and displayed in the Table of Items.

Rennur

Ok I see, do you have 3 objects, Project, ProjectVendor & Vendor?

Rennur

Maybe try:

FIND Vendor WHERE Vendor.ProjectType=ThisProjectTypeVendor.Project.ProjectType

FIND Vendor WHERE Vendor.Project.ProjectType=ThisProjectTypeVendor.Project.ProjectType

Cheers

ddumas

So, Vendor does not have have a ProjectType, or a Project.

There are the following objects - I should have made this more clear:

ProjectType - Type of Project, such as ERP, Networking, Java, etc

Vendor - SAP Consulting, Oracle Consulting, etc.

ProjectTypeVendor - This is an intersection of Vendor and ProjectType. There is a many to many relationship between Vendor and ProjectType. ProjectTypeVendor resolved this as an intersection (bridge) table. A Vendor in ProjectTypeVendor is authorized to participate in Projects of that particular ProjectType.

Project - A Project has one ProjectType, and can have many Vendors, but each Vendor for a Project must already exist in ProjectTypeVendor with the same ProjectType as Project.ProjectType. So, that is why ProjectVendors exist in Project. I just need the "Add New" on a ProjectVendor to filter for Vendors that exist in ProjectTypeVendor for a particular Project.ProjectType. Since Project is in context when I click the Add New, this should not be a problem (I wish).

So, in the suggestions you sent so far, those will not work, as the relationships in those FINDs do not exist. My original FIND should work, but it seems that AWAREIM cannot do the equivalent of a correlated subquery using a FIND. I wish I could code ANSI SQL (using parameters, resolved at runtime) directly into AWAREIM, but it seems that that feature has been deprecated, and replaced with stored procedures. Unfortunately, using stored procedures makes the application NOT portable between databases.

So, I am blocked at this point into finding a "hack" solution, unless there is a trick to code a proper FIND in AWAREIM, to perform the equivalent of a correlated subquery that I need.

I do not wish to make ProjectTypeVendor an attribute of ProjectVendor. That complicates the design, and I have never needed to do that sort of thing in other applications. A simple custom query to filter the Vendors should work, but does not.

I hope this explains things better.

ddumas

I got this to work via a "hack", in my opinion. I do not like it, as it is clunky, and does not match the rest of the application. I set the "Add New" for ProjectVendors to a custom process called AddProjectVendor, which takes Project passed in as context. In AddProjectVendor, I do a Pick, and then CREATE ProjectVendor. Screenshots below.

ddumas

This is still not working.

I changed the pick to something bogus in the WHERE clause (pick should not display ANY instances), to see if this is really working. The following should NOT display any instance in the PICK. I changed to <>, instead of =

PICK FROM ProjectTypeVendor WHERE ProjectTypeVendor.ProjectType <> Project.ProjectType

However, the PICK returns the same instances as when the = was used!

Very bad. So, it seems that PICK does not evaluate properly.

ddumas

Scratch my last reply. Thankfully the PICK is working. ProjectVendor had records satisfying the PICK, even with the <>. ProjectVendor had records for another ProjectType.

So, I do have a workaround for the original issue (FIND in custom query does not return results), but I still consider this workaround a "hack".

Dave

ddumas

Rennur wrote
The custom query filter only filters the ProjectVendors already created and assigned to the Project. You probably do not need a custom query filter in a Parent Child relationship since the Project will only display the Vendors anyway.

When creating new vendors using Add New, you are creating a new child object which will be linked to the parent (Project) automatically and displayed in the Table of Items.

I think I forgot to get back to you on this particular reply.

Thanks for clarifying the custom query on pre -existing instances only. Yes, I realize the the add new creates a child, and will automatically inherit the parent relationships. So, what it not working in the add new is that when I am in that form (adding a new ProjectVendor instance record ), the drop down for vendors shows all vendors if there is no filter set on the query. I need the drop down to filter for Vendors that exist in ProjectTypeVendor, for the in-context Project.ProjectType. So, I need a way to filter that Vendor dropdown list. Since the dropdown list is on Vendor (and not ProjectTypeVendor), I need a way to join to PRojectTypeVendor in a "correlated subquery". I am using that term in a SQL sense.

Sorry for the ling winded reply - just trying to explain better.

Dave

Rennur

If I understand correctly, Project & ProjectTypeVendor both have a relationship with ProjectType.

When you create a new Project you specify 1 ProjectType. This Project now has the ProjectType specified but does not yet have any ProjectTypeVendors. The Project can have many ProjectTypeVendors. This is assuming you have a peer (many-many) relationship directly with ProjectTypeVendors.

If so, you can filter ProjectTypeVendors for the project by:
FIND ProjectTypeVendors WHERE ProjectTypeVendors IN ThisProject.ProjectType.ProjectTypeVendors
OR
FIND ProjectTypeVendors WHERE ProjectTypeVendors.ProjectType.ID CONTAINS ThisProject.ProjectType.ID

I believe you have a bunch of ProjectTypeVendors already created in the system even before you created a new Project object.

Is the ProjectVendors attribute in the object Project is a reference attribute for ProjectTypeVendors or is this another intersection type object?

Cheers

Rennur

Going back to your first post with a somewhat clearer understanding about the structure, would this query work:

Instead of:

FIND Vendor WHERE Vendor IN ProjectTypeVendor.Vendor AND ProjectTypeVendor.ProjectType=Project.ProjectType

ProjectTypeVendor is not in context only Project is, so the query will not return results.

Try:

FIND Vendor WHERE Vendor IN ProjectVendors.Project.ProjectType.ProjectTypeVendors.Vendors

or something thereabouts.

Am I even close here :lol:

Cheers

ddumas

Rennur wrote

Going back to your first post with a somewhat clearer understanding about the structure, would this query work:

Instead of:

FIND Vendor WHERE Vendor IN ProjectTypeVendor.Vendor AND ProjectTypeVendor.ProjectType=Project.ProjectType

ProjectTypeVendor is not in context only Project is, so the query will not return results.

Try:

FIND Vendor WHERE Vendor IN ProjectVendors.Project.ProjectType.ProjectTypeVendors.Vendors

or something thereabouts.

Am I even close here :lol:

Cheers

Your relationship diagram is exactly correct. Good job!

So, in the ProjectVendor input screen, in the presentation for the Vendor dropdown, I set to custom filter, and coded as:

FIND Vendor WHERE Vendor IN ProjectVendor.Project.ProjectType.ProjectTypeVendors.Vendor

I got the following error:

So, even though ProjectTypeVendors.Vendor does NOT have multiple checked, ProjectVendors does. I like the FIND, as it seems to suggest that you can go up and down a "relationship tree". Unfortunately does not like it when it hits an object that allows multiple

Rennur

Maybe:
FIND Vendor WHERE Vendor.ProjectTypeVendors IN Project.ProjectType.ProjectTypeVendors