Sequential numbering of a found set on a monthly report
I have a solution in which I follow up artist bookings. For this I have among other a table called 'bookings'. Each booking has a separate unique kp_bookingID. Kp's aren't always sequential since bookings are sometimes deleted, gaps between the kp's can occur.
Each month I need to submit a report for tax purposes. Here's where I'm stuck. Each booking on this monthly report needs to have an ascending sequential number based on the booking date, say 2014_01 ; 2014_02; 2014_03 where the following number _01; _02; _03 would need to be generated automatically.
Here's an example
'booking date: 2014-01-04' 'booking made on 2014-01-01' > this one would need to be 2014_01 on the monthly report
'booking date: 2014-02-05' 'booking made on 2014-01-02' > this one would need to be 2014_03 on the monthly report
'booking date: 2014-01-25' 'booking made on 2014-01-03' > this one would need to be 2014_02 on the monthly report
I made a new table based on the bookings table called 'bookings_year' linked only by the year, this gives me the bookings for 2014 only, and I can sort them chronologically but I'm stuck on how fmpro could number them accordingly taking into account the numbers (_01;_02;_03 etc...) can not start anew each month, but only once a year. So for example on my monthly report in March we'd be somewhere like 2014_14 and higher...