Have you tried using a query based DE that checks to see if any records in Primary status exist and gives you the count? (maybe from a hidden field with a constant value of 1). Then do a validation to make sure that field is not greater than 1?
Yes I already tried this one. But it will fail in this scenario.
1. User creates 1 record with Primary Status successfully (Record 1).
2. User creates another record with Primary Status successfully (Record 2) since the count of primary status is still equal to 1. It seems that this record is not yet counted since it cannot tell yet what is the status of this record.
You need to add the value of the QBDE to a value on the record being created. So if there is one record with the status, the "Primary Status Counter" field =1. Add that to a field on the record being created (with a value of 1) or just add the QBDE +1 in a field. Do your validation against that field where the result cannot be greater than 1.
Or you do the QBDE and validate it cannot be greater than 0.
Thanks Sean Kirkwood - Gaea, I have tried this one, but I can't find a way to only perform QBDE+1 if the status of the new record is Primary.
Is there a way to only perform QBDE+1 if the newly created status is Primary, otherwise, it should not add 1?
Yes - under Configuration, you set up the query condition. The screenshot below shows a budget showing the Amount field (you would use your field holding the 1). Below, you set the Condition where the status is Primary. It should ignore non-primary status records. You might need to set some other conditions, but this should work.
Thank You for this one, however, this will not solve my issue where if a new record is created regardless of the status (Primary or Non Primary), the validation will still occur.
I would only like to display the validation if the user ties to create a new Primary record otherwise it should not be displayed.
I suggest you to create a Integer type field and use this new field for the BP status. Leave original status only with a dummy status. This way you can use the integer type pull down field on your validations.
Thanks Emre for this answer.