2 Replies Latest reply on Dec 11, 2013 11:54 AM by MaartenLeemans

    Create unique value on a condition

    MaartenLeemans

      Title

      Create unique value on a condition

      Post

           I made a database for my invoices. At the moment I'm adding an option to create a credit note. I have a field where I can select 'paid', 'not paid' or 'credit note'. If the last one is selected, I should create a unique value in a field called 'credit note number' (first time I do this, it should be 13001, 13002 for the next time, ... - every year I start again, so next year it should be 14001, ...). No number needs to be created if I don't select 'credit note'. 

        • 1. Re: Create unique value on a condition
          philmodjunk

               What you want is very similar to that  requested in the topic discussed today in this thread: Custom Serial Generation fails

          • 2. Re: Create unique value on a condition
            MaartenLeemans

                 I got it figured out, genius me ;-) I think I replace the overal serial (for every Invoice), with this script as it does not 'skip' numbers if I delete a record

                   
            •           Facturen_sj = self joined from Facturen
            •      
            •           Creditnotanummer = my new made serial
            •      
            •           jaar = last two digits from this year

                  

                 If [condition what fields need a serial]

                 If[ Left ( Max ( Facturen_sj::Creditnotanummer ) ; 2 )  = Facturen::jaar ] 

            // So if there is already a record this year //

                 Field Set [ Facturen::Creditnotanummer; Max ( Facturen_sj::Creditnotanummer) + 1]

                 Else

                 Field Set [ Facturen::Creditnotanummer; Facturen::jaar & Right ("001" ; 3)]

                 End If

                 End If