I’ve temporary table as datasoource in my form. I’ve a date control, initially my form wants to be empty and once I gave the date it needs to fetch the records based on the date from my temptable to form.
How should I do that?
Thanks.
So how/when you are inserting the data into the temp table?
we shld mention two dates and after that when we click that Populate Button data shld be inserted into Form.
To make it clear, Do you want to know how to insert the data into temp table based on the selected dates?
Here is the documentation of the same, www.axaptapedia.com/Temporary_tables
Let us know if you have a more specific question.
i didnt understood what is that tmpTableclass ? in that link.
public void init() {
super();
TempTable.setTmpData(tmpTableClass::populateTmpData());
}
You need to have code some where that inserts the data into temp table. In that example they are using a class with static method to populate the temp table. tmpTableClass is class. You can use the similar approach. Create a class with a static method that inserts the data into temp table and return temp table and call the method in the init method of the form.
delete_from tmpprodtable;
breakpoint;
while select trans
join prodTable where prodTable.ItemId == trans.ItemId
&& prodTable.InventRefId == trans.SalesId
&& prodTable.InventRefType == inventRefType::Sales
{
while select prodBom where prodBom.ProdId == prodTable.ProdId
{
select prodJournalBom where prodJournalBom.InventTransId == prodBom.InventTransId;
select inventDim;
tmpprodTable.ProdId = prodtable.ProdId;
tmpprodtable.SalesId = trans.SalesId;
tmpprodTable.ItemId = prodBom.ItemId;
tmpprodTable.BOMConsump = prodjournalbom.BOMConsump;
tmpprodtable.InventLocationId= inventdim.InventLocationId;
tmpprodtable.DeliveryDate = trans.DeliveryDate;
tmpprodtable.PackingSlipId = trans.PackingSlipId;
tmpprodtable.Revision = inventdim.InventColorId;
tmpprodtable.InventTransId = prodbom.InventTransId;
// tmpprodtable.
tmpprodtable.BOMQty = prodbom.BOMQty;
tmpprodtable.QtySched = prodtable.QtySched;
tmpprodtable.QtyBOMCalc = prodbom.BOMQty * prodtable.QtySched;
tmpprodtable.Variance = tmpprodtable.QtyBOMCalc - tmpprodtable.BOMConsump;
tmpprodtable.fDate = trans.DeliveryDate;
tmpprodtable.tDate = trans.DeliveryDate;
tmpprodtable.Name = trans.Name;//.Name;
tmpprodtable.UnitId = prodbom.UnitId;
tmpprodTable.insert();
//tmpprodtable_ds.refresh();
}
This is my code, Kranthi to insert data into tmptable…
this is the codings I used for filter:
In clicked method:
void clicked()
{
TmpProdTable_ds.query().dataSourceTable(tablenum(TmpProdTable)).clearRange(fieldnum(TmpProdTable,ProdId));
TmpProdTable_ds.executeQuery();
}
in executequery:
public void executeQuery()
{
;
QFilter = SysQuery::findOrCreateRange(TmpProdTable_q.datasourceTable(tableNum(TmpProdTable)),fieldNum(TmpProdTable,ProdId));
DeliverydateFilter = SysQuery::findOrCreateRange(TmpProdTable_q.datasourceTable(tableNum(TmpProdTable)),fieldNum(TmpProdTable,DeliveryDate));
if (FromDate.dateValue() !=dateNull() && todate.dateValue() !=dateNull())
{
DeliverydateFilter.value(SysQuery::range(FromDate.dateValue(),todate.dateValue()));
}
else
{
DeliverydateFilter.value(SysQuery::valueUnlimited());
}
super();
}
And in init to make the form empty:
public void init()
{
super();
TmpProdTable_q.datasourceTable(tableNum(TmpProdTable)).addRange(fieldNum(TmpProdTable,ProdId)).value(SysQuery::valueEmptyString());
}
So shall I need to create a class for this, right?
Moved the code that you are using to insert the data into a class method and call that method in init method. You don’t have to delete the data from temp table in your case.
In class what methods i need to include, Kranthi?
You don’t necessarily have to use a class, you could put the logic to a table or the form, but you defininetely must call the code inserting to the table and pass the result to setTmpData(), as explained above. Note that forms always runs on client, while you can choose whether you execute classes and table methods on client or server.
By the way, here are some additional comments regarding your code:
The code for populating the temporary table is very inefficient. Instead of making many individual requests to database (which takes a lot of time), join the tables you need to a single query.
“select inventDim” without any condition looks like a bug.
Regarding your code in the form, using findOrCreateRange() doesn’t make much sense after you removed the range. Either delete ramnges and then directly add ranges, or use findOrCreateRange() but then don’t bother with removing the existing range. In either case, keep the logic at a single place instead of having one part of it (clearRange()) in clicked().
here what is to be exactly pass to setTmpData()
You need to have a method that return the temp table buffer after inserting the data and pass that method.
Let me explain a little bit how to use temporary tables.
Imagine that you have two temporary buffers:
TempTable tmp1;
TempTable tmp2;
If you insert something to tmp1:
tmp1.Field1 = "value1";
tmp1.insert();
you can then query the tmp1 buffer and find the record there. But if you look into tmp2, you find it’s empty, because it’s a completely independent instance of TempTable.
But you could point both to the same data by setTmpData():
tmp2.setTmpData(tmp1);
And this is what you need to do in your form. You want the buffer used by the form datasource to point to the same data that you inserted in your method. Therefore your method will return a table buffer and you’ll pass this buffer to setTmpData().