Item Requirements form - Filter by creator

Hi,

I want to write a piece of code, so that whatever records are there in Item Requirements will filter on the Employee RecID of the user who is viewing it and shall display only if they match

I had created a new field called Originator and it stores automatically the RecID

this.Originator = Global::currentWorker(); – SalesLine table initvalue method

The above works fine and data is captured perfectly.

My objective is whenever user opens the Project form then he clicks on PLAN – ITEM REQUIREMENT, System should compare the value in the Originator field with the current user and display only if they match.

Next what i did is in the form ProjSalesItemReq -SalesLine:LinkActive , I added few lines and finally the complete code looks like as follows (Sorry, i am putting the complete code, so that you may get the clear picture)

void linkActive()

{

#define.star("*")

ProjId projId;

ProjInvoiceTable projInvoiceTable;

ProjWIPTable projWIPTable;

QueryBuildDataSource queryBuildDataSource = this.query().dataSourceTable(tableNum(SalesLine));

QueryBuildRange projIdCriteria = queryBuildDataSource.findRange(fieldNum(SalesLine,ProjId));

QueryBuildRange serviceOrderIdCriteria;

QueryBuildRange SalesLineOriginatorCriteria = queryBuildDataSource.findRange(fieldNum(SalesLine,Originator));//TP

boolean ret;

PSATmpStr sProjId;

ProjInvoiceProjId sProjInvoiceProjId;

#define.star(’*’)

while (projIdCriteria &&

(isConfigurationkeyEnabled(configurationKeyNum(Project3)) ? !m_oProjStatistic : true))

{

queryBuildDataSource.clearRange(fieldNum(SalesLine,ProjId));

projIdCriteria = queryBuildDataSource.findRange(fieldNum(SalesLine,ProjId));

}

queryBuildDataSource.clearRange(fieldNum(SalesLine,Originator)); //TP

SalesLineOriginatorCriteria = queryBuildDataSource.findRange(fieldNum(SalesLine,Originator));//TP

switch (element.args().dataset())

{

case (tableNum(SMAServiceOrderTable)):

serviceOrderTable = element.args().record();

projTable = ProjTable::find(serviceOrderTable.ProjId);

serviceOrderIdCriteria = queryBuildDataSource.findRange(fieldNum(SalesLine,ServiceOrderId));

if (!serviceOrderIdCriteria)

{

serviceOrderIdCriteria = queryBuildDataSource.addRange(fieldNum(SalesLine,ServiceOrderId));

}

serviceOrderIdCriteria.value(serviceOrderTable.ServiceOrderId);

queryBuildDataSource.addRange(fieldNum(SalesLine,ProjId)).value(projTable.ProjId);

queryBuildDataSource.clearRange(fieldNum(SalesLine,SalesId));

queryBuildDataSource.clearRange(fieldNum(SalesLine,LineNum));

break;

case(tableNum(ProjTable)):

projTable = element.args().record();

projId = projTable.ProjId;

if (isConfigurationkeyEnabled(configurationKeyNum(Project3)))

{

queryBuildDataSource.addRange(fieldNum(SalesLine,ProjId)).value(projTable.ProjId + #star);

queryBuildDataSource.addRange(fieldNum(SalesLine,Originator)).value(queryValue(SalesLine.Originator==currentWorker()));

}

else

{

queryBuildDataSource.addRange(fieldNum(SalesLine,ProjId)).value(projTable.ProjId+’*’);

}

break;

case tableNum(ProjInvoiceTable) :

projInvoiceTable = element.args().record();

while select ProjId from projTable

where projTable.ProjInvoiceProjId == projInvoiceTable.ProjInvoiceProjId

{

queryBuildDataSource.addRange(fieldNum(SalesLine,ProjId)).value(projTable.ProjId);

ret = true;

}

if (!ret)

{

queryBuildDataSource.addRange(fieldNum(SalesLine,ProjId)).value(SysQuery::valueNotEmptyString());

}

break;

case tableNum(ProjWIPTable) :

projWIPTable = element.args().record();

while select ProjId from projTable

where projTable.wipProject == projWIPTable.ProjId

{

queryBuildDataSource.addRange(fieldNum(SalesLine,ProjId)).value(projTable.ProjId);

ret = true;

}

if (!ret)

{

queryBuildDataSource.addRange(fieldNum(SalesLine,ProjId)).value(SysQuery::valueNotEmptyString());

}

break;

//Link from PSATmpProjFinancialStatistic

case tableNum(PSATmpProjFinancialStatistic):

if (isConfigurationkeyEnabled(configurationKeyNum(Project3)))

{

m_tProjStatisitc = element.args().record();

sProjId = m_tProjStatisitc.ProjViewId;

sProjInvoiceProjId = m_oProjStatistic.parmProjInvoiceProjId();

m_oCriteriaProjId.value(’’);

if(sProjInvoiceProjId)

m_oCriteriaProjInvoiceProjId.value(’’);

if(m_tProjStatisitc.ProjTotal == PSAProjTotal::None)

{

m_oCriteriaProjId.value(queryValue(sProjId));

}

else if(m_tProjStatisitc.ProjTotal == PSAProjTotal::LevelTotal)

{

if(m_oProjStatistic.psaParmDisplayLevel() == 0)

{

if(sProjInvoiceProjId)

m_oCriteriaProjInvoiceProjId.value(sProjInvoiceProjId);

else if(m_oProjStatistic.parmProjId())

{

if(m_oProjStatistic.parmProjTree())

m_oCriteriaProjId.value(sProjId + #star);

else

m_oCriteriaProjId.value(sProjId);

}

}

else

{

m_oCriteriaProjId.value(sProjId + #star);

}

}

else

{

if(sProjInvoiceProjId && m_tProjStatisitc.ProjTotal == PSAProjTotal::GrandTotal)

m_oCriteriaProjInvoiceProjId.value(sProjInvoiceProjId);

else

m_oCriteriaProjId.value(strLTrim(sProjId) + #star);

}

}

}

super();

// Set caption

if (!salesLine)

{

element.setCaptionText();

}

}

Posting the whole code didn’t make it clear at all; fortunately I managed to find your code there.

You’re setting a wrong range value. Look at this statement:

queryBuildDataSource
    .addRange(fieldNum(SalesLine,Originator))
        .value(queryValue(SalesLine.Originator==currentWorker()));

It says that you want to add range to field Originator (which is a record ID) with the value equal to the result of expression SalesLine.Originator==currentWorker(), which is true or false - it doesn’t make sense. (Furthermore the expression is based on SalesLine.Originator currently loaded in the form). This is not what you want. You want sales lines with Originator equal to the current worker, therefore the code should be .value(queryValue(currentWorker())).

You can also simplify your code with SysQuery::findOrCreateRange().

Dear Martin,

My apologies.

Thanks a lot for your solution. It works

Regards