Dynamic Values for a new Column

[syhussaini]

Hello Team,

I am writing to seek your help in building a new column on my List which should set values dynamically i.e. based on the date value of the other column in the list.

I have an Expiry Date Field and now I would like to show another column in the list where if the date is less than today the cell value should be set to Expired and if it is greater than or equal to today it should set to Active.

Currently I am filtering out the expired items, however, I would like to show them but with the respective status.

I would also like to achieve a way color the rows that have the status "Expired".

Can someone please guide me.

PS: I am a newbie with a fast learning ability, kindly consider adding steps and I will certainly follow.

Looking forward to hearing soon.

Best Regards,
Syed H

 

[Sophist]

When you display a form, you want a calc element to decide whether it is expired or not.

By default, this is calculated when you load the form, when you save the form and when you display the list. The problem is that if you have a large list, then when you display the list doing the calcs on every row individually can take a long time. So, what you need to do is in the Calc settings, set:

Only Calc on Save = Yes (so it doesn't calc on List)
Ajax calculation = Yes (so calc on load works)
Calc on load = Yes (so form value is correct when loaded directly)​

Then on the list add a PHP Events plugin, and for the onPreloadData event write some PHP to issue an SQL update statement which:

a. Calculates whether the value should be Active or Expired
b. Updates all rows to this value where the actual value does not equal the calculated value (you don't want to update the rows where the value is already correct for performance reasons).

If you need help with the onPreloadData, I can provide you with some example code from my own site (which does a different update but the concept is the same).

 

[syhussaini]

Dear Sophist,

A big thank you for this quick and helpful inputs. I sincerely apologize for being so dumb, however, I cannot figure out how and where to perform these steps inside the Fabrik.
Can you please help me with steps and sample.

Best Regards,
Syed H

 

[Sophist]

To create the calc you create a new element and select the calc element type. Then you need to write a small about of php to test the expiry date against the current date and return either "Expired" or "Active".

To create the list php event SQL you need to edit your list, goto plugins add a php event plugin, go to the onPreLoadData edit box and enter code similar to the following:

// Update Fealties as count of users in that estate

$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);

$value = 'IFNULL(' . $db->quoteName('user_fealties.count') . ', 0)';

$query->clear()
  ->update($db->quoteName('rp_estates'))
  ->set($db->quoteName('rp_estates.fealties') . ' = ' . $value)
  ->where($db->quoteName('fealties') . ' <> ' . $value);

$db->setQuery($query);
try 
{
  $db->execute();
}
catch (Exception $e) 
{
  JFactory::getApplication()->enqueueMessage('Update of estate fealties failed: ' . $e->getMessage(), 'error');
}

Obviously you will need to change $value to MySQL code which compares the expiry date column to the current date and sets "Expired" or "Active" like the php does, and change the $query and other stuff to your own table.

 

[Sophist]

Obviously I cannot write your Fabrik site for you - so persevere up the learning curve and you will get there.

 

[syhussaini]

Dear Sophist,

Thank you again for thsi amazing help. I am sure I am doing something wrong which is why I am getting "Updated Failed: Unknown Column" error.

Here is what I did:

• Installed Calc and PHP Event Plugins and enabled them
• Created a Status Element with type calc and used the following code

$activeStatus= "Active";
$expiredStatus = "Expired";

if('{testing_list___expiry_date}' >= today)
{
return $activeStatus;
} else {
return $expiredStatus;
}

• Now I went into the List Editing Mode and under plug-ins used the following code under onPreLoadData block

$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);

$status_value = 'IFNULL(' . $db->quoteName('testing_list___expiry_date.count') . ', 0)';

$query->clear()
  ->update($db->quoteName('testing_list'))
  ->set($db->quoteName('testing_list.testing_list___status') . ' = ' . $status_value)
  ->where($db->quoteName('testing_list___status') . ' <> ' . $status_value);

$db->setQuery($query);
try
{
  $db->execute();
}
catch (Exception $e)
{
  JFactory::getApplication()->enqueueMessage('Update failed: ' . $e->getMessage(), 'error');
}

I am attaching the images of my list "testing_list" and elements for your guidance.



Looking forward to gaining your expert help.

Best Regards,
Syed H

 

[syhussaini]

Dear Sophist,

I tried the following code in List Edit onPreLoadData Area

$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);
$today = date('d.m.Y',strtotime("-1 days"));
$activeStatus= "Active";
$expiredStatus = "Expired";

if('{testing_list___expiry_date}' > $today)
{
return $activeStatus;
} else {
return $expiredStatus;
}

$query->clear()
  ->update($db->quoteName('testing_list'))
  ->set($db->quoteName('testing_list.status') . ' = ' . $status_value)
  ->where($db->quoteName('status') . ' <> ' . $status_value);

  $db->setQuery($query);
try
{
  $db->execute();
}
catch (Exception $e)
{
  JFactory::getApplication()->enqueueMessage('Update failed: ' . $e->getMessage(), 'error');
}

and the following in calc element

$activeStatus= "Active";
$expiredStatus = "Expired";
$today = date('d.m.Y',strtotime("-1 days"));

if($('{testing_list___expiry_date}') >= $today)
{
return $activeStatus;
} else {
return $expiredStatus;
}

This time, I did not get any error, however, the values for the Status column are blank and no status shows there.

Website Link: http://jobwalkins.in

Please help.

Best Regards,
Syed H

 

[Sophist]

I think that your problem is that you are not doing proper date comparisons either in the calc or list update.

The format that the date is displayed in the date picker UI is not the format it is stored in the placeholder. So in the calc you are comparing say a YYYY-MM-DD format string with a d.m.Y format string. If you are going to do a string compare you must put year before month before day to make it work, but it is far better to do a proper date comparison. The online php manual or other internet sites can give you details on this.

The same holds true for SQL comparisons.

The other problem is that your SQL update seems to have the same code in it as the calc. The update doesn't work the same way and you should not have the same code in it. I was looking to see what $status_value was set to, but the assignment is missing. This may just be a typo in the post here.

Hope this helps.

 

[syhussaini]

Dear Sophist,

I thank you for this generous help. I tried with little tweaks, however unable to get the desired results and ends up in Error

Error
Update failed: Unknown column 'Active' in 'where clause'

I have the following code under onPreLoadData block:

$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);
$today = date('Y-m-d',strtotime("-1 days"));
$activeStatus = "Active";
$expiredStatus = "Expired";

if('{testing_list___expiry_date}' > $today)
{
$status_value = $activeStatus;
} else {
$status_value = $expiredStatus;
}

$query->clear()
  ->update($db->quoteName('testing_list'))
  ->set($db->quoteName('testing_list.status') . ' = ' . $status_value)
  ->where($db->quoteName('status') . ' <> ' . $status_value);
 
  $db->setQuery($query);
try
{
  $db->execute();
}
catch (Exception $e)
{
  JFactory::getApplication()->enqueueMessage('Update failed: ' . $e->getMessage(), 'error');
}

and the following under status column (calc) field:

$activeStatus = "Active";
$expiredStatus = "Expired";
$today = date('Y-m-d',strtotime("-1 days"));

if($('{testing_list___expiry_date}') >= $today)
{
return $activeStatus;
} else {
return $expiredStatus;
}

I request your expert guidance on setting this code right in order to make it work.

Looking forward to hearing from you soon.

Best Regards,
Syed H

 

[Sophist]

Re the calc, as I have said before you need to do proper date comparisons - look up php date objects.

And as I have said before, the update PHP should not have the calc code in it - you need to change my original $status_value line to MySQL code which does the comparison between a table column and today's date and returns "Active" or "Expired". Off the top of my head it would be something like (by which I mean this WILL probably be wrong but close):

$status_value = "IF(`testing_list`.`expiry_date` < CURRENT_DATE, 'Expired', 'Active')";

 

[syhussaini]

Dear Sophist,

Thank you for being so patient with me. I just did small changes the code as follows:

Status Column (Calc) field code:

$today = date("Y-m-d");
$expire = $row->expiry_date; //from db

$activeStatus = "Active";
$expiredStatus = "Expired";

if($expire < $today)
{
return $activeStatus;
} else {
return $expiredStatus;
}

List Edit "onPreLoadData" block code:

$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);
$today = date('Y-m-d');

$status_value = "IF(`testing_list`.`expiry_date` < $today, 'Expired', 'Active')";

$query->clear()
  ->update($db->quoteName('testing_list'))
  ->set($db->quoteName('testing_list.status') . ' = ' . $status_value)
  ->where($db->quoteName('testing_list.status') . ' <> ' . $status_value);
 
  $db->setQuery($query);
try
{
  $db->execute();
}
catch (Exception $e)
{
  JFactory::getApplication()->enqueueMessage('Update failed: ' . $e->getMessage(), 'error');
}

Now I do not have any errors, however, the status column is blank and no value is showing up.
I am sure I am making some silly mistakes. Can please help me here.

Best Regards,
Syed H

 

[Sophist]

If you take what I write and then do something illogical with it, then funnily enough it won't work.

$expire=$row->expiry_date;//from db ??????? Where did this come from?

$status_value="IF(`testing_list`.`expiry_date` < $today, 'Expired', 'Active')"; ??????? Where did $today come from?

When testing, first test the form and make sure that when you edit the form and change the expiry date then the calculation works correctly.
Then using phpmyadmin, clear the calculated field values and then display the Fabrik list and they should be repopulated.

I have given you as much help as I can - you need to get the rest of the way yourself - or buy a Pro subscription and let Hugh fix it for you.

 

[syhussaini]

Dear Sophist,

I sincerely thank you for all the advice and help so far. I know I acted unexpectedly foolish. I would love to gain the professional help by buying the subscription, however, I would like to know if I would get help to with the code as I am unable to make it work.

Looking forward to hearing from you soon.

Best Regards,
Syed H