1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

  2. The forum you are viewing relates to Kayako Classic. If you signed up or upgraded to the new Kayako (after the 4th July 2016), the information in this thread may not apply to you. You can visit the forums for the new Kayako here.

Auto increment a custom field

Discussion in 'Style and design' started by Todd H, Jan 22, 2016.

  1. Todd H

    Todd H Member

    Hi All,

    We are looking at using Kayako (with custom fields) to track our RMAs.

    I have figured out a custom field structure that should support this, however our current system auto assigns the RMA number automatically. Our current RMA numbering is basically RMA-number, where 'number' is the automatically generated value.

    There's a number of ways of going about this, but I would like to avoid having to assign these numbers manually in order to ensure uniqueness.

    One option that would work would be to simply use the ticket number with RMA as a prefix (i.e. 'RMA-ticket number), but I'm not sure if there is an easy way to include the number from another database field as part of a default value, or to even just create this combined value in the background and display it in the custom field area of the ticket so the warranty techs can easily see the RMA number as part of the ticket.

    Any thoughts or suggestions?

    Thanks,
    Todd
     
  2. Gary McGrath

    Gary McGrath Staff Member

    Hi Todd,

    How do you see this working, I assume your staff will be in a customer ticket, and then need to generate an RMA number due to the issues in the ticket? if thats the case, you could use an integration app ( https://kayako.atlassian.net/wiki/display/DEV/Creating your first Kayako App ) to add a new TAB to the viewing ticket screen ( where it has general, reply, forward etc.. ). You could make this new TAB offer the option to go and generate a RMA number off of your other system, and have it return the result of that to a custom field stored in Kayako.

    All of the above might sound fairly complicated, but Kayako has good examples of using application integration which you can learn from. The only unknown bit is your other system, if it has an API, then this should be easy.

    If coding is not your strong point, there are quite a few 3rd party developers you can engage on the forums to help you code it :)

    Gary
     
  3. Todd H

    Todd H Member

    Thanks for the suggestion Gary!

    You are correct that our RMA numbers are currently being generated in another system (a highly marketed system that shall remain nameless), but we would like to move our RMA process completely out of that system and use Kayako exclusively to store our RMA data, so I'm hoping we can come up with a strategy that doesn't require a 3rd party app interface.

    One option that I was thinking of would be to create an RMA number using the Kayako ticket number (i.e. RMA-kayakoticketnumber), or perhaps just use an integer increment function to generate RMA numbers sequentially (RMA-10001, RMA-1002, RMA-1003, etc.).

    I'm not sure how to reference (or copy) data from an existing field into a custom field though to achieve that.

    Do you think this (or something similar) is achievable without too much custom coding within the Kayako framework?

    As always your feedback is much appreciated.

    Thanks,
    --Todd
     
  4. Gary McGrath

    Gary McGrath Staff Member

    Hi Todd,

    Ok I follow you, I think using the ticket number here is probably the best option, it is already an auto incrementing number ( even if you are using it as ticket mask, in the background there is still a ticket ID number )

    If you are matching the ticket ID with the RMA, do you actually need to store an actual RMA number if it's the same? You could just have a custom field saying "ticket has RMA" and have it as a yes/no option.

    If you do want to repeat the number, it's should be possible to amend the code in the edit tab on a ticket to copy the current ticket ID to the custom field.

    Gary
     
  5. Todd H

    Todd H Member

    Hi Gary,

    That sounds like a good approach. Setting up code to copy the ticket ID into the custom field would be ideal, as it will make it easier for the shipping technicians to locate the RMA as it shows up then with the addresses and names of the recipients.

    What code would you recommend modifying in the 'edit' tab to accomplish this?

    As always thanks for your assistance.

    Thanks,
    Todd
     
  6. Gary McGrath

    Gary McGrath Staff Member

    Hi Todd,

    In the file class.View_Ticket.php ( _apps\ticket\staff )

    There is a function called RenderTicket, within that is a section called Begin General tab

    In there somewhere you will want to grab the ticket ID and then make it set the value of the custom field in question.

    It is probably easier just to make it hard set the value with an SQL statement rather than trying to scan the form contents and find the field.

    e.g. use $this->Database->Query("");

    Gary
     
  7. Todd H

    Todd H Member

    Hi Gary,

    Thank you for the suggestion.

    After looking at the code and the way our system is set up (the RMA number is tied to a custom field group that only applies to tickets that are assigned to the RMA department), I'm wondering if a better approach might be to set up a special button or similar that can be placed near the custom field on the edit form that when pressed will pull the ticket number from the database as you described above and insert the ticket number into the 'RMA Number' custom field area on the form similar to how the 'default value' feature does, and it will get inserted to the database once the ticket is updated.

    I'm thinking something like this would be required since not all tickets will have an RMA number, and there is no record for the custom field 'RMA number' in the database until a value gets entered on the form and the 'update' button gets pressed.

    Does this sound like a feasible / practical solution?

    Any insight would be welcome.

    Thanks,
    Todd
     
  8. Gary McGrath

    Gary McGrath Staff Member

    Hi Todd,

    You can certainly do that, in the same file, just keep scrolling down, you will see it builds each tab one by one, once you get down to the edit tab, you can add the code there.

    You can use the swift interface code to add another button, I guess on the button you will simply want to use java script. You "might" need to add your own controller however. ( not sure if Java script on it's own will be able to do the job for you )

    It might be easier to simply use the code in the general tab as I suggested, but simply add a check, if custom field exists, set it to the ticket ID. You could even add further qualification and state if department = rma and custom field does not exist, add it ( you can use my sql statement to add it too fairly easily )

    Gary
     
  9. Todd H

    Todd H Member

    Hi Gary,

    The approach described in the quote above looks like the right strategy to pursue.

    Here's my initial attempt at the SQL/PHP code to update the RMA record value, have a look and let me know what you think:

    $this->Database->Query("UPDATE " . TABLE_PREFIX . "customfieldvalues SET fieldvalue = " .
    $this->Ticket->GetProperty('ticketid') . " where customfieldid = 9 AND typeid =" . $this->Ticket->GetProperty('ticketid');

    I've done a fair amount of work with SQL, but not so much with PHP, so I suspect there are some corrections required to what I have put together.

    If you have any additional example code you think might help I would certainly be interested in having a look!

    As always your help is greatly appreciated.

    Thanks,
    Todd
     
  10. Gary McGrath

    Gary McGrath Staff Member

    Hi Todd,

    That looks to me like it should work for existing tickets which have that field added. ( assuming the customfield ID is correct )

    Was it not working?

    Gary
     
  11. Todd H

    Todd H Member

    Hi Gary,

    Thanks for the quick reply - I haven't actually tried it yet as I thought I'd better get a second opinion first since it's writing into the database.

    I'll try it out tonight and let you know what the results look like!

    Thanks,
    Todd
     
  12. Todd H

    Todd H Member

    Hi Gary,

    I was able to get some positive results -- here is the final version of the code that I inserted around line 350 that updates the RMA number in the database:

    $this->Database->Query("UPDATE " . TABLE_PREFIX . "customfieldvalues SET fieldvalue = 'RMA-" . $_SWIFT_TicketObject->GetTicketID() . "' where customfieldid = 9 AND typeid = " . $_SWIFT_TicketObject->GetTicketID());

    Initial testing indicates that just that code by itself may be enough to achieve what I am looking for.

    If I create a new ticket in the RMA department, it will create the RMA number automatically.

    If I create a ticket in a different department, and then move it into the RMA department, it will add the RMA number as soon as the 'update' button is pushed even if nothing is typed into the RMA field. If something is typed into the RMA field, it ends up getting overwritten with the autogen RMA number.

    The real test will be next week with the support team -- however at the moment I think we may have got it figured out.

    If you see any issues with the code above, please let me know.

    Thanks as always for your support!
    Todd
     
  13. Gary McGrath

    Gary McGrath Staff Member

    Hi Todd,

    The code above looks fine, since it's a DB query, it will either work if it's there, or fail if it's not. Which is what you are looking for anyway I think :)

    Gary
     
    Todd H likes this.

Share This Page