• 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.

Calc option (UUID) + Javascript disable element

Status
Not open for further replies.

jo-ka

Member
Hello.

I need to find a way to use a calc element (ServiceOrderGUID), that will just do the calculation when the field was no previously filled and based on a yes/no element (watch element)

Let me explain better:

I will have a form where I will receive support tickets. In this form, I have a Yes/No element where the back-end user will decide if this ticket will generate a service order or not.

So, if the ticket doesn't generate any service order (option 0 from Yes/No element), this calc field will be empty. If the element is set to Yes (option 1), then this calc will generate a new GUID on the Service Order table.

Then, with this new calculated field, I will use the upsert table to input the new GUID on the Service Order table, which generate a link between the 2 forms.

The problem is that the PK of the Service Order table is an GUID type field, it's not an traditional incremental ID field and I can't change this, because it's part of an ERP system, so my calc element is doing something like this, remember, watching ServiceOrderYesNo for AJAX update:

if('{fab_tickets___ServiceOrderYesNo}' == 1)
{
$db = JFactory::getDbo();
$db->setQuery('SELECT UUID( )');
$uuid = $db->loadResult();
return $uuid;
}
else {
return null;
}


This works on new records (of course) and the UUID changes all the times the page is refreshed or the record is edited.

I cant' find a way to have the calc returning the field value that was previously saved, once the YES/NO option is set to 1 (Yes), when editing the record or refreshing the page so I can guarantee that the GUID doesn't change.

Also, but I think that I need to use some JS, I need to disable the YES/NO button, once the user set the option to YES, because once the Service Order is created in the other table, I can't loose the link between the 2 tables.

Tried with JS option but couldn't do it also. My approach for JS was something like:

On Load, if the ServiceOrderGUID field is not empty, then ServiceOrderYesNo is disabled.

Didn't worked.

Is my approach completely wrong? Is there a better way to do this?

Thanks in advance.
 
I'd do both the UUID generation and upserting with a form submission plugin, running onAfterProcess, not a calc & upsert.

Something like ...

Code:
if ($formModel->formDataWithTableName['fab_tickets___ServiceOrderYesNo_raw'] === '1') {
   if (empty($formModel->formDataWithTableName['fab_tickets___uuid_raw'])) {
      $db = JFactory::getDbo();
      $query = $db->getQuery();
      $query->update('fab_tickets')->set('uuid = UUID()')->where('id = ' . (int)'{rowid}');
      $db->setQuery($query);
      // uncomment this one time for testing
      // var_dump((string)$query);exit;
      $db->execute();
      $query->clear()
         ->insert('fab_serviceorder AS s')
         ->leftJoin('fab_tickets AS t ON t.id = ' . (int)'{rowid}')
         ->set('s.uuid = t.uuid');
      $db->setQuery($query);
      // uncomment this one time for testing
      // var_dump((string)$query);exit;
      $db->execute();
   }
}

Obviously change table / field names to suit.

If you need to do updating on edit to update rather than insert, add that as an 'else' on the empty() test.

-- hugh
 
Or in the calc, check whether the existing value of the calc element is blank and if it is create a new guid otherwise return the existing value.
 
But I agree with Hugh in principle - if possible you should let the upsert create the gui and then store that in the calc field rather than let the calc field create the guid without checking that it is unique in the upsert field.
 
I'd do both the UUID generation and upserting with a form submission plugin, running onAfterProcess, not a calc & upsert.

Something like ...

Code:
if ($formModel->formDataWithTableName['fab_tickets___ServiceOrderYesNo_raw'] === '1') {
   if (empty($formModel->formDataWithTableName['fab_tickets___uuid_raw'])) {
      $db = JFactory::getDbo();
      $query = $db->getQuery();
      $query->update('fab_tickets')->set('uuid = UUID()')->where('id = ' . (int)'{rowid}');
      $db->setQuery($query);
      // uncomment this one time for testing
      // var_dump((string)$query);exit;
      $db->execute();
      $query->clear()
         ->insert('fab_serviceorder AS s')
         ->leftJoin('fab_tickets AS t ON t.id = ' . (int)'{rowid}')
         ->set('s.uuid = t.uuid');
      $db->setQuery($query);
      // uncomment this one time for testing
      // var_dump((string)$query);exit;
      $db->execute();
   }
}

Obviously change table / field names to suit.

If you need to do updating on edit to update rather than insert, add that as an 'else' on the empty() test.

-- hugh

Dear Hugh, thank's for your suggestion.

I assume that you are suggesting to use the PHP form plugin, right?
I've tried to use this code, OnAfterProcess , with the necessary changes, but I can't still not make it work. I really don't know why. Then I've tried a simple table update and also I couldn't make it work...

I've the made this on other table, just for testing:

$db = JFactory::getDbo();
$query = $db->getQuery();
$query->update('fab_tickets')->set('User = ' . $db->quote('23'))->where('id = ' . (int)'{rowid}');
$db->setQuery($query);
$db->execute();

But also didn't worked... I have this error message: 1054 Unknown column 'enabled' in 'where clause'
What am I doing wrong?

Thank's in advance.
 
It's probably the missing 'true' arg to getQuery, which should be:

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

If you don't use that arg, you get the current query object cached in the database object, that has whatever query was last executed in it. The 'true' says "create a new query object". And obviously the last query has a where clause using 'enabled' in it.

-- hugh
 
The second line in the code you quoted in your previous post is missing (true).

Looks at the line I gave you, and compare it to your line.

-- hugh
 
Dear Hugh,
I've been struggling to get this working, but now I can see the light at the end of the tunnel. So, in parts:

1 - In deed it was missing the true statement here $query = $db->getQuery();. So I've changed the code, but still had problems. After some tests, I've found that in the first if condition ,

if ($formModel->formDataWithTableName['fab_tickets___ServiceOrderYesNo_raw'] === '1') {

I would need to use the element name without _raw. So now my first part of the code is like this, and it's working for fab_tickets table, with all tests made, selecting yes or no, inserting new records, updating, etc. Perfect.

if ($formModel->formDataWithTableName['fab_tickets___ServiceOrderYesNo'] === '1') {
if (empty($formModel->formDataWithTableName['fab_tickets___uuid_raw'])) {
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->update('fab_tickets')->set('uuid = UUID()')->where('id = ' . (int)'{rowid}');
$db->setQuery($query);
// uncomment this one time for testing
// var_dump((string)$query);exit;
$db->execute();


So now the second part, because I think this code may not be correct. This is because the serviceorder table (the ERP table) don't have a typical id field. The table PK is a UUID element, which is generated everytime a record is saved. Also, and I might be wrong, on your code you are trying to join fab_tickets table to itself: leftJoin('fab_tickets AS t ON t.id = ' . (int)'{rowid}')

When I try to execute this code, I receive a 1604 error: You have an error in your SQL syntax;... ... set s.uuid = t.uuid'at line 1

$query->clear()
->insert('serviceorder AS s')
->leftJoin('fab_tickets AS t ON t.id = ' . (int)'{rowid}')
-> Is this correct?
->set('s.uuid = t.uuid');
$db->setQuery($query);
// uncomment this one time for testing
// var_dump((string)$query);exit;
$db->execute();

}
}

Anyway, in my opinion what we need is just a simple insert/update into serviceorder table, something like:

->insert('serviceorder')->set('uuid = ' . $db->quote('{fab_tickets___uuid}'));

and after this, the remaining fields I would need, but for testing purposes this is enough.

I've tried this, with and without _raw but it didn't worked. So I think I'm close, but I'm missing something...

What do you think?

Thanks in advance.
 
>insert('serviceorder')->set('uuid = ' . $db->quote('{fab_tickets___uuid}'));

That won't work, because the {fab_tickets___uuid} placeholder doesn't exist. We just created that value by hand by updating the fab_ticket row by hand, so internally, Fabrik knows nothing about it. Hence the leftJoin, to get that value from the row we just updated.

If that doesn't work, try looking up the UUID we updated by hand, and use that ...

Code:
      $query->clear()->select('`uuid`')->from->('fab_tickets')->where('id = ' . (int)'{rowid}');
      $db->setQuery($query);
      $uuid = $db->loadResult();
      $query->clear()
         ->insert('fab_serviceorder AS s')
         ->set('s.uuid = ' . $db->quote($uuid));
      $db->setQuery($query);
      // uncomment this one time for testing
      // var_dump((string)$query);exit;
      $db->execute();
 
OK, tried this.

Now, If I use the second part of the code, the first part doesn't work, this is, the UUID is not generated on fab_tickets___ ServiceOrderGuid... Also, if I uncomment the var_dump lines, the code doesn't break... Am I doing something wrong?

So, here's the full code, with my correct table names:

if ($formModel->formDataWithTableName['fab_tickets___ServiceOrderYesNo'] === '1') {
if (empty($formModel->formDataWithTableName['fab_tickets___ServiceOrderGuid_raw'])) {
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->update('fab_tickets')->set('ServiceOrderGuid = UUID()')->where('id = ' . (int)'{rowid}');
$db->setQuery($query);

// uncomment this one time for testing
// var_dump((string)$query);exit;

$db->execute();
$query->clear()->select('ServiceOrderGuid')->from->('fab_tickets')->where('id = ' . (int)'{rowid}');
$db->setQuery($query);
$uuid = $db->loadResult();
$query->clear()
->insert('serviceorder AS s')
->set('s.Guid = ' . $db->quote($uuid));
$db->setQuery($query);

// uncomment this one time for testing
// var_dump((string)$query);exit;

$db->execute();
}
}
 
There's a syntax error (that's the problem with writing code directly in the forums).

Code:
$query->clear()->select('ServiceOrderGuid')->from->('fab_tickets')->where('id = ' . (int)'{rowid}');

... should be ...

Code:
$query->clear()->select('ServiceOrderGuid')->from('fab_tickets')->where('id = ' . (int)'{rowid}');

-- hugh
 
Damn, I spent about an hour trying to find a typo and couldn't find the -> after from.

Ok, the first part of the code is now working again, but the second part now returns this error:

1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS s SET s.Guid = 'xxxxxx'' at line 1

So I've changed the code like this without the AS s, and it's working... curious or not...

$query->clear()
->insert('serviceorder')
->set('Guid = ' . $db->quote($uuid));

Do you think it might be something about I'm using MariaDB and not MySQL?
 
Now that this is working as expected, I need to link both Guid fields.

Is there a way to link both fields, like a dbjoin element, which display the service order number (after I change the label on dbjoin of course) but without the possibility for user to change it?

Something like just a number with a link to the service order itself, but with the dropdown disabled, with the possibility to click on it and be forwarded to the service order.

I don't know if I made myself clear...

Thanks in advance.
 
I've added a calc element, with a custom link like this, hidding the ServiceOrderGuid field:

$db = JFactory::getDBO();
$sql="SELECT Number FROM serviceorder ";
$sql.="INNER JOIN fab_tickets ON fab_tickets.ServiceOrderGuid = serviceorder.Guid ";
$sql.="WHERE fab_tickets.id = ";
$sql.="'{fab_tickets___id}'";
$db->setQuery($sql);
return $db->loadResult();


Custom link:
index.php?option=com_fabrik&task=form.view&formid=10&rowid={fab_tickets___ServiceOrderGuid_raw}

This will do the job for the list view.
Is there a way to have a link also, in this calc field, but on the fab_tickets form record?
 
I think I was able to find a way, if there's a easier way, please post.

I've changed the return code as this, adding the custom link concatenated with the result...

return '<a href="index.php?option=com_fabrik&task=form.view&formid=10&rowid={fab_tickets___ServiceOrderGuid_raw}">'.$db->loadResult().'</a>';

Thanks in advance.
 
That's usually how I build custom links that I need in details/form view as well as list view. Just build the link directly in a calc.

-- hugh
 
That's usually how I build custom links that I need in details/form view as well as list view. Just build the link directly in a calc.

-- hugh
One last question... Do you use to store the calc results on database or not? What's the best practices?
 
Status
Not open for further replies.
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top