• paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Topic: Transaction Reference Source?

I started using FrontAccounting with a Work Order Transaction Reference to add the month to the end {MM}. The work order number would auto increment in December similar to 00112, 00212, 00312.

  I have since thought I did not need the Month indicated in the work order number so I changed the Trans Ref to just {001}. When I create a new work order, the number is now 12004, 12005, 12006, etc...

  I am using a prefix that I wish to not change. I can change the prefix (create a new reference) and the new format is followed after the new prefix. Is there a way to reset the transaction reference and still keep the prefix?

Thanks.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Transaction Reference Source?

Set the next reference to be 12001 in the 0_refs table.
Check with the following SQL for next references:

SELECT * FROM 0_refs ORDER BY RIGHT(`reference`, 4), `type`, `id`;

The above assumes the 0_reflines.pattern to be {001}/{YYYY} which is the default.

apmuthu's Website

  • paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Re: Transaction Reference Source?

Thanks for the reply, apmuthu, but this does not seem to work. I tried your suggestion before starting this post and it did not work.

While I don't understand all of the code for obtaining the next number, I find this after I start a new work order when I have the Transaction References set to 3 for prefix followed by {YY}{01}{MM}:

SELECT MAX(CAST(SUBSTR(wo_ref, 4,LENGTH(wo_ref)-5) AS UNSIGNED)) FROM `workorders` tbl
                    LEFT JOIN voided v ON tbl.`id`=v.id AND v.type='26' WHERE ISNULL(v.id) AND `wo_ref` REGEXP '^319[0-9]*12$'

  It looks like it is looking in the workorders table to extract the next number and the resulting number is 3198612

  When I change the Transaction Reference to 3 {YY}{01}, I get this:

SELECT MAX(CAST(SUBSTR(wo_ref, 4,LENGTH(wo_ref)-3) AS UNSIGNED)) FROM `workorders` tbl
                    LEFT JOIN voided v ON tbl.`id`=v.id AND v.type='26' WHERE ISNULL(v.id) AND `wo_ref` REGEXP '^319[0-9]*$'

  The result is 3191287. I was hoping to get 31987.

  • paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Re: Transaction Reference Source?

If I go into the workorders table and rename all existing work orders to eliminate the last two digits (12), then my new Transaction Reference is used correctly.