Autogenerating Number
Hi guys,
I have a question for autogenerating number when I create a new record.
So I have 2 fields which are Reference and Type. Please see below photo:
"Type" is the multiple choices option ...
Forum Discussion
Ah - if the sequence is specific to that one project and not a global sequencing then you'll have to go a different route. Best way is to make a formula query field that counts the # of records with the same type related to that same parent record where the Record ID# is less than the current record (basically how many came before it of the same type/parent) and create the sequence #. Then copy that value via Pipeline.
------------------------------
Chayce Duncan
------------------------------
For Example,
Project A
Cost Records - Provisional Sum
1.01 - $100
1.02 - $200
Cost Records - Variations
2.01 - $300
2.02 - $400
Cost Records - Anticipated Variations
3.01 - $500
3.02 - $500
Project B
Cost Records - Provisional Sum
1.01 - $200
1.02 - $300
Cost Records - Variations
2.01 - $400
2.02 - $400
Cost Records - Anticipated Variations
3.01 - $600
3.02 - $600
Project C
Project D
etc
Hope you understand what I want now.
If I can use formula to do it, can you please show me how to do?
Thank you very much!
------------------------------
Harris Tse
------------------------------
This is just a rough idea, you will need to fill in and modify to your exact system
var text rootNumber = Case([Type Field],
"Provisional Sum","1",
"Variations","2",
"Anticipated Variations","3",
"");
var number counter = Size(GetRecords("{'3'.LT.'" & [Record ID#] & "'}AND{'Parent Field FId'.EX.'" & [Related Parent Value] & "'}AND{'Type Field Fid'.EX.'" & [Type] & "'}",Dbid())) + 1;
$rootNumber &"."&PadLeft(ToText($counter),2,"0")
You would need to swap the actual FID values for the related field and type field that would make the query valid.
------------------------------
Chayce Duncan
------------------------------Hi Chayce,
I have input the formula as above.
var text rootNumber = Case([Type],"Provisional Sum","1","Variations","2","Anticipated Variations","3","");var number counter = Size(GetRecords("{'3'.LT.'" & [Record ID#] & "'}AND{'Parent Field FId'.EX.'" & [Related Project] & "'}AND{'Type Field Fid'.EX.'" & [Type] & "'}",Dbid())) + 1;$rootNumber &"."&PadLeft(ToText($counter),2,"0")Is there anything I need to change?and it show something like this?Do I need pipeline?
------------------------------
Harris Tse
------------------------------You will need to actually update the formula with your fields and your Field IDs. 'Parent Field FId' for example is just a placeholder that you need to replace with the actual Field ID of the 'related' field that defines your parent / child relationship. So for example if your relationship is based on 'Related Project' - you need to go into the field settings and find the fid of Related Project and swap it out in your formula query.
As for the Pipeline - you will need to create a new one that is triggered on record creation, and grab the value the formula field that you are creating to generate the new reference. Lets say you call that 'Reference Auto Generate'. In your pipeline - your 'action' will be to update the record that was just created and to copy the value of Reference Auto Generate into your Reference field so it is saved permanently.
*Note you could use a form rule if you prefer - the same method applies as the pipeline in the case where you have the auto generated field on the form and when the record is saved push it into the Reference field.
------------------------------
Chayce Duncan
------------------------------
