• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

update_col with upsert

jo-ka

Member
Hello

I'm using an update_col plugin on a list to update a status field on that list, as the image shows.
View attachment 17332

I'm also using the ability to email user on update date and update user functions, so I'm happy with it.

But I also need to update a field on a different list when the user click this button. I tried to set an upsert on the form but, as far as I understand, this plugin doesn't use the form post method to execute.

Is there a way to achieve this?

Thanks in advance.
 
You'll have to handle that yourself in the post-update PHP code for the update_col.

The row data for the selected rows will be in $data, as an array of groups of arrays of row objects (even if your list isn't grouped, it'll still be a single entry array). So something like ...

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

foreach ($data as $group) {
   foreach ($group as $row) {
      // you can now access $row->yourtable___yourelement, PK will be in $row->__pk_val
      $query->clear()->update('sometable')->where('some_fk = ' . (int)$row->__pk_val)->set('some_field = ' . $db->quote($row->yourtable___something));
      $db->setQuery($query);
      $db->execute();
   }
}

return "some informative message";

You can return text which will be shown in a popup on the list when the update_col completes.

As usual, depending on the element type (if it has the concept of 'label' and 'value', like dropdowns or joins, etc), you may need to use the _raw name, $row->yourtable___something_raw.

-- hugh
 
Thanks hugh.
I'm trying to accomplish this but I might be missing something.

So, to be clear, I must put the code on the Post Eval field on the update separator of the update_col plugin, right?
Considering that it's correct, let's assume this scenario...

My origin list is fab_originlist and have for example 3 fields: id | number | status
My destination list is fab_destination and have 4 fields: id | number | name | status

So, my SQL query would be something like (I know the query is not correct because I need the JOIN, but it's just to simplify):

update fab_destination set fab_destination.status = fab_origin.status where fab_destination.number = fab_origin.number

So my PK on origin table is fab_origin.number and my fk on destination is fab_destination.number

When using your code:

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

foreach ($data as $group) {
foreach ($group as $row) {
// you can now access $row->yourtable___yourelement, PK will be in $row->__pk_val
$query->clear()->update('fab_destination')->where('number = ' . (int)$row->__origin.number->set('status = ' . $db->quote($row->fab_origin___status));
$db->setQuery($query);
$db->execute();
}
}

return "some informative message";

I thing I'm making something wrong with the __pk_val placeholder. Is this OK with just 2 __? shall I prepend the PK with the table name like fab_origin___number?

Sorry for this, but I'm not able to make this work.

Thanks in advance.
 
If your "joining" column is fab_destination.number = fab_origin.number (so not FK to PK, you couldn't use this as join in a Fabrik list but you can use it in a query) I think your code should be
$query->clear()->update('fab_destination')->where('number = ' . (int)$row->fab_origin___number->set('status = ' . $db->quote($row->fab_origin___status));
 
If your "joining" column is fab_destination.number = fab_origin.number (so not FK to PK, you couldn't use this as join in a Fabrik list but you can use it in a query) I think your code should be
$query->clear()->update('fab_destination')->where('number = ' . (int)$row->fab_origin___number->set('status = ' . $db->quote($row->fab_origin___status));

OK, I was able to get this working... sort of.

The problem is that the value that is passed to the destination table is the list loaded value and not the updated one. I need to update the destination table with the new value updated by the update_col plugin.

Is this possible?
 
Ah, yes, the $data array won't have the updated data, it'll just have the data as read from the table prior to applying the updates.

So you'll have to join the original row ...

Code:
$query->clear()
   ->update('fab_destination')
   ->leftJoin('fab_origin ON fab_origin.number = fab_destination.number)
   ->where('fab_origin.number = ' . (int)$row->fab_origin___number)
   ->set('fab_destination.status = fab_origin.status');

This assumes 'number' is a unique foreign key, ie. each table only has one, they are a one-to-one relationship.

-- hugh
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top