FrontAccounting forum — Transaction Reference Source?

Re: Transaction Reference Source?

null@example.com (paul) — Wed, 18 Dec 2019 17:16:34 +0000

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.

Re: Transaction Reference Source?

null@example.com (paul) — Wed, 18 Dec 2019 17:12:13 +0000

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.

Re: Transaction Reference Source?

null@example.com (apmuthu) — Wed, 18 Dec 2019 04:45:50 +0000

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.

Transaction Reference Source?

null@example.com (paul) — Tue, 17 Dec 2019 20:26:32 +0000

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.