Limit the fields shown in a dropdown based on a relation

I have a table that uses the projid as a relation meaning when it is clicked as a dropdown it lists all projects in the company. What I’m looking to do is limit the fields that appear in the dropdown based on what values are already held in the table.

For example. if in my table a record already exists for a projid, it doesn’t list it’s corresponding project in the dropdown. Is this possible?

Create a lookup form helper. It talks about it some here: http://kashperuk.blogspot.com/2010/01/lookup-form-returning-more-than-one.html

The basic idea is you just create your own form that is shown with whatever you want behind it. You can also add specific relations on the EDT if that can be done in your case.

Is there anyway to do this that only effects one lookup within the form? The form I’m making will be to create a new property and I’m attempting to only list projects that don’t have a property already linked to them through the projid on the proptable I’ve created. The rest of the form would just be blank with property details for the user to fill out.

You could perhaps create another EDT called ProjIdCustom or something and extend ProjId. Then add the “FormHelp” to your ProjIdCustom, so it only affects that one.

I’ve solved this now. I setup a method in projtable with this code.

public client static void unassociatedProjIdLookup(FormStringControl _ctrl)
{
SysTableLookup projIdLookup = SysTableLookup::newParameters(tableNum(ProjTable), _ctrl);
Query query = new Query();
QueryBuildDataSource qbds = new QueryBuildDataSource();
QueryBuildRange unusedProjId;
ProjTable projbuffer;
PropTable propbuffer;
;

qbds = query.addDataSource(tableNum(ProjTable));
projIdLookup.addLookupfield(fieldNum(ProjTable, ProjId),true);
projIdLookup.addLookupfield(fieldNum(ProjTable, Name));

while select projbuffer notExists join propbuffer where projbuffer.ProjId==propbuffer.ProjId
{
if(str2int(projbuffer.ParentId)==0)
{
qbds.addRange(fieldNum(ProjTable, ProjId)).value(Projbuffer.ProjId);
}
}

projIdLookup.parmQuery(query);
projIdLookup.performFormLookup();
}

And overrode the lookup in the form calling that method.