Export a ListOption or Dynamic Custom Fields

[kirondedshem]

(table structure)sale:date,product_name,quantity,price.
I have the above table which holds sold quantity and price for a given product name for different dates, the product names can repeat and can be different depending on external user entry.

I would like to have an exportable list/report where every product quantity & price is in its own column for each distinct product, something like:
Date,maize_qty,maize_price,rice_qty,rice_price,-------,product_n_quantity,product_n_price
so that i can export it

I have tried the following

1. I can get all distinct dates and show them in a list, then loop trhough all individual products and create custom ListOptions in (ListOption_Rendered event) for quantity & price Columns, Problem is the list options do not export
2. The other alternative I throught of was to to create custom fields dynamically depending on how many distinct products I have in my table.

So Qun:

1. How do I export custom ListOptions
2. Is it possible to add custom fields dynamically and make them exportable

NOTE: I know how to create a manual custom file and do the neccessary loops and create the list, but i would prefer if its has all those phpmaker enable fetaures like multiple export format,pagination already configured for me

[arbei]

1. How do I export custom ListOptions

PHPMaker will hide all the ListOptions_Item when Export because the scripts do not know what the content are. If they are data, you can create a view or create custom fields.

You can use Page_Exporting, Row_Export and Row_Exported Server Event to export in your own way.

1. Is it possible to add custom fields dynamically and make them exportable

You cannot add custom fields dynamically, but you can create them first and then set the content by Row_Rendered server event or show/hide them by server event.

Read help file topic: "Project Setup" -> "Custom Fields" for more information.

[kirondedshem]

I finally got around this and thought I should share. In the end I had to create redundant custom fields, for example
I approximated and knew I would have like 30 distinct products and hence 30 columns for quantity and 30 columns for price looking like
maize_qty,maize_price,rice_qty,rice_price,-------,product_n_quantity,product_n_price.

I create my custom field in format of product_1_quantity,product_1_price,product_2_quantity,product_2_price, -------- ,product_n_quantity,product_n_price.
Juts ensure to create as many as you feel neccessary.

BUT the problem was each value of these had its own way of being queried from the db, which I could not set in the CustomField->Expression since it changes depending on what products are available int he system and what product is positioned where, So i had to do the following

1. Set the Column headers dynamically depending on what product you want to be in what position.
2. Customise the Custom Feild Expression into an sql expresiion to get the exact value required for the exact product on the header.

NOTE: I had used row redering and ListOptions to show this info before, but since they all have to run a query each time i want to get a given value to display for a given row and column, I found it too slow and I saw this was better since I just have to change the sql phpmaker will use for this list to what i want to to appear where
So in List page load I put:

// Page Load event
function Page_Load() {
//echo "Page Load";

        //GTE THE SQL to be executed for this table
    $sql = $this->getSqlSelect();
	        
                     //LOOP THROUGH EACH PRODUCT AS YOU ASSIGN IT TO ONE OF THE CUSTOM COLUMNS
		//echo "Page Load";
		$TheQuery = "select id,name from product";
		$result = ew_Execute($TheQuery)  or die("error during: ".$TheQuery);
		$rows = $result->GetRows();

                    //DECLARE A COUNTER FOR ALL THE CUSTOM FIELDS
		$custom_field_ind = 1;
		foreach ($rows as $row )
		{
                            //DO THIS AS LONG AS THERE ARE CUSTOM FIELDS TO BE USED, I had 33 sets at the time
			if($custom_field_ind <= 33)
			{
                            //keep id of the product so as to pass it in query
			$pdt_id = $row["id"];
			$pdt_name = $row["name"];
                            
                            //DETERMINE THE COLUMN NAMES OF EACH SET OF THE CUSTOM FIELDS TO ASSIGN TO A PRODUCT
			$qty_field = "product_".$custom_field_ind."_quantity";//eg product_1_qauntity,product_2_qauntity,----,product_n_qauntity
			$prc_field = "product_".$custom_field_ind."_price";//eg product_1_price,product_2_price,----,product_n_price

			//CHANGE THE COLUMN HEADER CAPTIONS OF EACH FIELD to desired product name quantity or desired product name price
                            //eg Maize Qty, Beans Qty etc etc
			Language()->setFieldPhrase(CurrentPage()->TableName, $qty_field, "FldCaption", $pdt_name." Qty");
                           //eg Maize Price, Beans Price etc etc
                            Language()->setFieldPhrase(CurrentPage()->TableName, $prc_field, "FldCaption", $pdt_name." Price");

			//CUSTOMISE THE SQl expresiion for each custom field to get the desired value of assigned product

//for example find if 1 is the id of my product and I want to get the quantity sold for this product on this exact sale record then egt " '' AS 'product_1_quantity' " and //replace it with " (select sd.ca_quantity from sale_detail sd where sd.product_id = 1 and sd.sale_id = sale.id) AS product_1_quantity "
$sql = str_ireplace("'' AS $qty_field","(select sd.quantity from sale_detail sd where sd.product_id = $pdt_id and sd.sale_id = ".CurrentPage()->TableName.".id) AS $qty_field",$sql);
$sql = str_ireplace("'' AS $prc_field","(select sd.ca_price from sale_detail sd where sd.product_id = $pdt_id and sd.sale_id = ".CurrentPage()->TableName.".id) AS $prc_field",$sql);
//go to the next set of custom fields
$custom_field_ind++;
}
else{break;}
}

		//set new sql which has the exact queries required to return ecah individual value for a specified product within a specified row
		$this->setSqlSelect($sql);

}