Need to display a field from Salestable to report

Hi,

I’ve added a display method in my report as:

display str getsearchname()
{
SalesTable stab;
ProdTable prod;
ProdJournalRoute pjr;
;
/* select pjr
join prod
join stab
where stab.SalesId==prod.InventRefId && pjr.ProdId== prod.ProdId;*/
select SalesId from stab
join InventRefId from prod
where prod.InventRefId == stab.SalesId
join ProdId from pjr
where pjr.ProdId == prod.ProdId;
return stab.SearchName;

}

but the searchname field isn’t showing in my report.

Thanks.

You are not selecting SearchName and you are not saying for which prodId you need the search name.

select firstonly SearchName from stab
existsjoin prod
where prod.InventRefId == stab.SalesId
&& prod.ProdId == prodJournalRoute.ProdId; // prodJournalRoute is the data source name in the report

still its empty, Kranthi

can you show your code?

display str getsearchname()
{
SalesTable stab;
ProdTable prod;
ProdJournalRoute pjr;
;
/* select pjr
join prod
join stab
where stab.SalesId==prod.InventRefId && pjr.ProdId== prod.ProdId;*/
select firstonly SearchName from stab
exists join prod
where prod.InventRefId == stab.SalesId
&& prod.ProdId == prodJournalRoute_1.ProdId;
return stab.SearchName;

}

You should use the debugger to debug your code; discussion forums aren’t a replacement of debugger.

First check if your code returns the right value or not. If not, running the report is a waste of time; it can’t ever show anything if the method doesn’t provide any value.

If it does return the value, looking at this code is useless, because the problem is somewhere in how you display the value in the design.

By the way, please use Insert > Insert code in the rich formatting view to paste source code and throw away unrelated things such as the code you’ve commented out. Compare the readability with what you posted above:

display str getsearchname()
{
	SalesTable stab;
	ProdTable prod;

	select firstonly SearchName from stab
		exists join prod
		where prod.InventRefId == stab.SalesId
		   && prod.ProdId == prodJournalRoute_1.ProdId;
		   
	return stab.SearchName;
}

Try debugging.

it showing recid as 0 when I debug, Kranthi.

Still in debugger, look at if you have a value in prodJournalRoute_1.ProdId. If not, the query can’t ever return anything.

no, Martin.
Its showing 0 in that Prodid…

Where/How you are using that method?

In report, kranthi

I’m dragging that field into the body…

I think you are using same approach in getItemId method, to get the prodTable. Then how it is working?
Try debugging both methods.

As there is a method in ProdJournalRoute to Prodtable

ProdTable prodTable(boolean _forUpdate = false)
{
    return ProdTable::find(this.ProdId,_forUpdate);
}

And in find method of ProdTable

static ProdTable find(ProdId      prodId,
                      boolean     _forUpdate = false)
{
    ProdTable prodTable;
    ;
    prodTable.selectForUpdate(_forUpdate);

    if (prodId)
        select firstonly prodTable
            index hint ProdIdx
            where prodTable.ProdId == prodId;

    return prodTable;
}

it returns the item name record, Kranthi.

there is no relation directly for salestable to Prodjournalroute, Kranthi.

For that reasoning we are using ProdTable in between.
You are using same ProdId in getSearchName method. That’s why i asked you to debug those methods as well.

select SearchName from stab
    exists join prod
    where prod.InventRefId==stab.SalesId
    && prod.ProdId==prodJournalRoute_1.ProdId;
    

Here in prodJournalRoute_1.ProdId in Prodid its showing value but when I hover on prodJournalRoute_1 its showing 0

If you have the correct value in prodJournalRoute_1.ProdId, then you should get the SearchName from salesTable.
Do you really have the related data in SalesTable? Is your production order created from the sales order?

see this screenshot. Kranthi…