Auto fill a field with all records in another table's field

dantanie · Post by **dantanie** » Wed Feb 07, 2018 10:53 pm

Hi Fellow members, can you kindly assist me to solve this problem.

So I have the three tables "Names" "Data" and "Stats"
Name table contains the following fields.
NameID | Name

Inside the Name Table, I have the following records.
James
Arnold
McDonald's
Raymond
David
Richard

Data Table contains the following fields
DataID | Date |

Stats Table Contains the following fields
StatsID | NameID | Score | PointsGained

I made Data a master table and related a connection to the Stats Table between Fields "DataID" ==== "StatsID"

So on the Add page for Data Table, inputs for Data shows in Normal Edit (Tabula form)
and input for Stats shows in Grid Edit Mod.

Now, this is where on the Add page when a user Selects or enter a date into the Date field.
I want the "Name" field in the Stats Table to gets auto-populated with records from the Names Table, Field "Name".

This way the user will already have the names populated so they only add records to the remaining fields, "Score & PointsGained"

Below is a link to screenshot examples I have seen on other software
test.myghbiz.com/Example1.png
test.myghbiz.com/Example2.png

Any Ideas and Help will be highly appreciated.
Thank you for your help

mobhar · Post by **mobhar** » Thu Feb 08, 2018 10:34 am

Always post your complete tables schema and some example of records in each of table for more discussion.

kirondedshem · Post by **kirondedshem** » Thu Feb 08, 2018 1:15 pm

I feel all you need if while on master/detail add the detail records will auto populate them selves with relevant data and just wait for extra input for other fileds but the list of detail add records should be automatically determiend depending on some value on master table or something like that.
The topic s below impliment automatically fillinf detail tbales so you juts have to add you rlogic around them.
Refer to these topics
viewtopic.php?f=4&t=40867.

OR

viewtopic.php?f=4&t=40961

dantanie · Post by **dantanie** » Fri Feb 09, 2018 3:50 am

I followed exactly the steps at post viewtopic.php?f=4&t=40867

Step 1

On details table -> Server Events - Listpage - Pageload()
function Page_Load() {
//set count to number of products
$this->GridAddRowCount = ew_ExecuteScalar("select count(*) from itemsdb");
}

Step 2
On details table -> Server Events - Table specific -Common - Row_Rendring()
function Row_Rendering() {
// Enter your code here

if($this->PageID == "grid")
{
$grid_count = $this->GridAddRowCount;
$grid_num = $this->GridCnt;
if(isset($this->RowIndex))
{
$grid_num = $this->RowIndex;
//only when we are dawing actual rows
if(($grid_count >= $grid_num) && is_int($grid_num) && ($grid_num >= 1))
{
$offseter = $grid_num - 1;
//get & set product details
$product = ew_ExecuteRow("SELECT ItemName FROM itemsdb limit 1 OFFSET $offseter");
$this->Product->CurrentValue = $product["ItemName"];

//hide grid delete
$this->ListOptions->Items["griddelete"]->Visible = FALSE;

}
}
}
}

When I run the app, it adds the rows count, but it does not fill the Product field with the Item name. It's only showing Please select.
Please, can anyone review my code above and tell me what am doing wrong?

Thank you.

dantanie · Post by **dantanie** » Fri Feb 09, 2018 6:47 am

Ok, I figured it out.
I don't know for whatever reason but the script wasn't working because "IF" Function

if($this->PageID == "grid")
.

Now my question is before the field "ItemName" gets populated with the code above, I use to do it manually with field lookup table. Where it autofill other fields. But I Realise using the method above to auto-fill all records does affect the auto-fill function.

Please, any ideas how to make that work?
Thank you

dantanie · Post by **dantanie** » Fri Feb 09, 2018 9:24 am

Great, Figured that out also by re-tweaking the code.

function Row_Rendering() {
// Enter your code here

//if($this->PageID == "grid")
//{
$grid_count = $this->GridAddRowCount;
$grid_num = $this->GridCnt;
if(isset($this->RowIndex))
{
$grid_num = $this->RowIndex;
//only when we are dawing actual rows
if(($grid_count >= $grid_num) && is_int($grid_num) && ($grid_num >= 1))
{
$offseter = $grid_num - 1;
//get & set product details
$product = ew_ExecuteRow("SELECT ItemName FROM itemsdb limit 1 OFFSET $offseter");
$purprice = ew_ExecuteRow ("SELECT PurPrice FROM itemsdb limit 1 OFFSET $offseter");
$whprice = ew_ExecuteRow ("SELECT WhSale FROM itemsdb limit 1 OFFSET $offseter");
$this->Product->CurrentValue = $product["ItemName"];
$this->RetailPrice->CurrentValue = $purprice["PurPrice"];
$this->WhSalePrice->CurrentValue = $whprice["WhSale"];

//hide grid delete
$this->ListOptions->Items["griddelete"]->Visible = FALSE;

}

}
}

kirondedshem · Post by **kirondedshem** » Fri Feb 09, 2018 12:42 pm

//get & set product details
$product = ew_ExecuteRow("SELECT ItemName FROM itemsdb limit 1 OFFSET $offseter");
$purprice = ew_ExecuteRow ("SELECT PurPrice FROM itemsdb limit 1 OFFSET $offseter");
$whprice = ew_ExecuteRow ("SELECT WhSale FROM itemsdb limit 1 OFFSET $offseter");
$this->Product->CurrentValue = $product["ItemName"];
$this->RetailPrice->CurrentValue = $purprice["PurPrice"];
$this->WhSalePrice->CurrentValue = $whprice["WhSale"];

I feel you can optimise this a bit, since you are running ew_Excuterow multiple time, if you have alot of rows it might become a problem on performance, you can also just do it one like so:

//get & set product details
//et the row once with all values
$itemsdb = ew_ExecuteRow("SELECT ItemName,PurPrice,WhSale FROM itemsdb limit 1 OFFSET $offseter");
$this->Product->CurrentValue = $itemsdb["ItemName"];
$this->RetailPrice->CurrentValue = $itemsdb["PurPrice"];
$this->WhSalePrice->CurrentValue = $itemsdb["WhSale"];

kirondedshem · Post by **kirondedshem** » Fri Feb 09, 2018 12:52 pm

I don't know for whatever reason but the script wasn't working because "IF" Function
if($this->PageID == "grid")

phpmaker pages have various PageIDs depending on what action its performing like add,edit,view,list,grid,delete etc. Now depending on your seeing wether you are using this code on a detail table in master/detail relationship or if you are using this on a given tables grid add. If you still use the same table to maybe later view a list or go to normal add page etc. Then it would be wise to know when to run this code and when not to.

Coz Row_rendering event is used by all pages on that table so you dont want to impliment this code on pages other than the those having the exect form you want. SO thats why I put that if statement so Its only used i=when page is of ID grid(meaning when used in maybe master detail add form as a detail table).

So you can var_dump $this->PageID in row_rendering to know what page id that from shows so you only limit this fucntionalilty while on this page. But you dont really have to if it works well for you, I ust wanted to caution you.

dantanie · Post by **dantanie** » Sat Feb 10, 2018 9:20 am

Thank you a lot
The page id was (gridcls) and now the code is working with IF function.