1. Fabrik 3.8.1 has been released. It is mostly bug fixes and feature enhancements, but does include two new plugins (push notifications, and the sequence element). As usual we strongly recommend testing the new release on a sandbox if your application is mission critical, and always do an Akeeba backup before updating.

Calc option (UUID) + Javascript disable element

Discussion in 'Standard Support' started by jo-ka, Jan 11, 2018.

Thread Status:
Not open for further replies.
  1. jo-ka

    jo-ka Member

    Level: Standard
    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.
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    I'd do both the UUID generation and upserting with a form submission plugin, running onAfterProcess, not a calc & upsert.

    Something like ...

    Code (Text):

    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
     
  3. Sophist

    Sophist Well-Known Member

    Level: Community
    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.
     
  4. Sophist

    Sophist Well-Known Member

    Level: Community
    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.
     
  5. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Just don't use a calc, period. Or an upsert. Do it all in one hand-rolled submission plugin.

    -- hugh
     
  6. jo-ka

    jo-ka Member

    Level: Standard
    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.
     
  7. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    It's probably the missing 'true' arg to getQuery, which should be:

    Code (Text):

    $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
     
  8. jo-ka

    jo-ka Member

    Level: Standard
    OK, I'll try this.
    But I'm not sure I understand the quoted sentence. Can you explain me better please?
     
  9. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    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
     
  10. jo-ka

    jo-ka Member

    Level: Standard
    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.
     
  11. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    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 (Text):

          $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();
     
     
  12. jo-ka

    jo-ka Member

    Level: Standard
    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();
    }
    }
     
  13. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    There's a syntax error (that's the problem with writing code directly in the forums).

    Code (Text):

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

    Code (Text):

    $query->clear()->select('ServiceOrderGuid')->from('fab_tickets')->where('id = ' . (int)'{rowid}');
     
    -- hugh
     
  14. jo-ka

    jo-ka Member

    Level: Standard
    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?
     
  15. jo-ka

    jo-ka Member

    Level: Standard
    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.
     
  16. jo-ka

    jo-ka Member

    Level: Standard
    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?
     
  17. jo-ka

    jo-ka Member

    Level: Standard
    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.
     
  18. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    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
     
    jo-ka likes this.
  19. jo-ka

    jo-ka Member

    Level: Standard
    OK, so this case is closed now.

    Thanks.
     
  20. jo-ka

    jo-ka Member

    Level: Standard
    One last question... Do you use to store the calc results on database or not? What's the best practices?
     
Thread Status:
Not open for further replies.

Share This Page