Problem Returning Current Row ID on Calc in New Record

[AMTI]

I just ran into a problem:

I have a form that needs to have the current row ID concatenated with a prefix. I'd like to have it in a new record but i'll settle by hiding it until the record is recorded.

I used the Calc Element to produce the following in the calculation:

$number = 'ECN-';
$number .= '{eng_eco_request___id}';
return $number;

But it will not return the correct calculation, until after a edit and not save. In other words. I have to save the record. Edit it and save it again for any ID to show up in my concatenation.

So, my question is: Is there a way to grab a literal mysql current row number on a potential new record row and save it into the form without using the {tablename___field} method and how? Or any other method of getting to my goal would be great.

Is there a beter way of handling this than calc? I thought about using display but i don't understand Eval (yes/no) and default with that element.

Thanks in advance,
Trip

 

[cheesegrits]

Do you mean when you first load a new form, before you save it?

Or are you saying that when you first save a new form, it isn't writing the correct result to the underlying field in the MySQL table for the calc element?

-- hugh

 

[cheesegrits]

Do you mean when you first load a new form, before you save it?

Or are you saying that when you first save a new form, it isn't writing the correct result to the underlying field in the MySQL table for the calc element?

-- hugh

 

[AMTI]

Do you mean when you first load a new form, before you save it?

Yes, how would you display/show the literal actual potential current row ID.

Or are you saying that when you first save a new form, it isn't writing the correct result to the underlying field in the MySQL table for the calc element?

Yes, the calc element works for all things except ID. As stated before:

In other words. I have to save the record. Edit it and save it again for any ID to show up in my concatenation.

 

[jfquestiaux]

The calc element will try to do its calculation before the form is saved that is, for a new record, before the ID is created. That is why you don't have anything the first time and only the id when you edit/re-save the form.
If you need to record this calculation in this form, you'll have to use a PHP form plugin, set on onAfterProcess to acheive this.

 

[AMTI]

Thank you for your reply.
What should I put into the onAfterProcess PHP code to achieve this. An example pointing me in the right direction would be very helpful.

I have this in my onAfterProcess code:

$newNumber = 'ECR-';
$newNumber .= '{eng_eco_request___id}';
$formModel->updateFormData('eng_eco_request___number_raw',$newNumber);

But nothing happens. I've also tried using just:

$formModel->updateFormData('number',$newNumber);

Because I read in the Wiki that onAfterProcess use short element names no table names, but that didn't work either.

The element i'm using number is a calc element, but i've been trying it on different elements with no luck.

I've also tried using this:

$newNumber = 'ECR-'
$newNumber .= $formModel->_formData[id];

And echo'd that out to test, and the concatenation does work, but I can't seem to update the field with that new value.

$newNumber = 'ECR-';
$newNumber .= $formModel->_formData[id];
$formModel->updateFormData('eng_eco_request___number_raw',$newNumber,true);

Any suggestions?

 

[AMTI]

When I echo the formModel->_formData the information exists in the array but its never posted to the database??? This really shouldn't be so hard.

Here's the data from the array for everything after the save:

    [submit] => Save     [view] => form     [join] => Array         (         )      [id] => 1001     [date_time] => 2038-01-19 00:00:00     [job_number] =>      [project_name] =>      [cm_plan] =>      [received_date] =>      [requested_by] =>      [received_by] =>      [ticket] => fix this     [id_raw] => 1001     [number_raw] =>      [date_time_raw] => 2038-01-19 00:00:00     [creator_raw] => Array         (             [0] => 29         )      [job_number_raw] =>      [project_name_raw] =>      [cm_plan_raw] =>      [received_date_raw] =>      [requested_by_raw] =>      [received_by_raw] =>      [ticket_raw] => fix this     [creator] => Array         (             [0] => 29         )      [number] =>      [eng_eco_request___id] => 1001     [eng_eco_request___id_raw] => 1001     [eng_eco_request___number] => ECR-1001     [eng_eco_request___number_raw] => ECR-1001

 

[cheesegrits]

Well, if you stop and think for a moment about what's going on, it's actually not that easy. Or at least, we don't have a built in way for you to do this.

When you "Add" a new form, the row doesn't exist in the database until AFTER you have submitted it, and we insert the new row into the table. And there is no way of getting the 'rowid' (primary key value) from MySQL ahead of time. That only gets assigned when you do an INSERT on a table, which we obviously do as (pretty much) the very last thing during form submission.

So by the time the rowid is available for a new form, the processing of the form is complete, and all Fabrik related database writes have been done. That's why the hook you are using for the plugin is called "onAfterProcess".

So, you'll have to update the row Fabrik just wrote out yourself, either by directly running a query "by hand" yourself using the standard J! database API, or calling our code to do it.

Probably easier to run the query yourself. Something like this:

$rowid = $formModel->getRowId();
if (!empty($rowid)) {
   $db = JFactory::getDBO();
   $newNumber = "ECR-" . $rowid;
   $query = $db->getQuery(true);
   $query->update('eng_eco_request')->set("number = '$newNumber'")->where("id = $rowid");
   $db->setQuery($query);
   $db->query();
}

If you wanted to pad your 'number', like ECR-00004, ECR-00375, do ...

   $newNumber = sprintf("ECR-%05d", $rowid);

... replacing 5 with whatever the total zero padded character width you want.

-- hugh

 

[cheesegrits]

OBTW, remember to set your PHP plugin to only run on "New". Once the row exists and you are editing and submitting it, the calc will be able to do it's thing without you having to manually set it.

Oh, and I just had an idea of how we might be able to easy the pain of doing this. It's not a really common thing to need to do, but quite a few people have need to do something similar. I raised a "new feature" ticket yesterday on this:

https://github.com/Fabrik/fabrik/issues/668

... and I'm just about to update that with the thought I just had that might make the whole thing a little easier.

-- hugh

 

[AMTI]

Thank you for your guidance.
I was finally able to get it by amending your code to assign the rowid from the formModel->_formData[id];

The final code ended up being this:

$rowid = $formModel->_formData[id];
if (!empty($rowid)) {
   $db = JFactory::getDBO();
   $newNumber = "ECR-" . $rowid;
   $query = $db->getQuery(true);
   $query->update('eng_eco_request')->set("number = '$newNumber'")->where("id = $rowid");
   $db->setQuery($query);
   $db->query();
}

Thank you again for your continued help and willingness to go the extra mile for the Fabrik user community. This is why I love Fabrik and working with you guys.

Sincerely,
Trip