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 which are shown below: What I want to set up is when I select Prov Sum, the record will start from 1.01, 1.02 etc. If I select Variation, the record will start from 2.01, 2.02 etc. If I select Anticipated Variation, the record will start from 3.01,3.02 etc. Every projects will be the same for the above sequence. Currently, I am inputting manually. Is this something I can do it by formula or pipleline? Thank you. ------------------------------Harris Tse------------------------------

Your intent is to sequence them correct? So if this is the 5th 'Variation' then it should be 2.05? Your best solution is to make sure that your 'Type' is it's own table. So the table would have your list of Types and then you create a relationship to whatever table is being used to select that and they're choosing the 'Related Type'. With that, you can summarize the # of records created for that 'Type' - add that field as a lookup and create the 'next reference number which would be lookup value + 1. Then using Pipelines when the record is saved, copy a formula field that you create the 'Next Reference Number' and move it into the manual field where it can stay stored permanently. ------------------------------Chayce Duncan------------------------------

Hi Chayce, Yes, my intention is to sequence them and the number will come up automatically. So I have 2 tables, one is "Project" and one is "Cost" which show each project cost records (PS, Variations, Anticipated Variations). "Project" is the parent table while "Cost" is the child table. Each project will have many cost records and I want the sequence like above. In this case, can I still do it in formula or pipeline? Thanks. ------------------------------Harris Tse------------------------------

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------------------------------

Autogenerating Number | Qrew Discussions

ChayceDuncan
Qrew Captain
7 months ago
Your intent is to sequence them correct? So if this is the 5th 'Variation' then it should be 2.05?

Your best solution is to make sure that your 'Type' is it's own table. So the table would have your list of Types and then you create a relationship to whatever table is being used to select that and they're choosing the 'Related Type'. With that, you can summarize the # of records created for that 'Type' - add that field as a lookup and create the 'next reference number which would be lookup value + 1. Then using Pipelines when the record is saved, copy a formula field that you create the 'Next Reference Number' and move it into the manual field where it can stay stored permanently.

------------------------------
Chayce Duncan
------------------------------
- HarrisTse
  Qrew Member
  7 months ago
  Hi Chayce,
  
  Yes, my intention is to sequence them and the number will come up automatically.
  
  So I have 2 tables, one is "Project" and one is "Cost" which show each project cost records (PS, Variations, Anticipated Variations). "Project" is the parent table while "Cost" is the child table. Each project will have many cost records and I want the sequence like above.
  
  In this case, can I still do it in formula or pipeline?
  
  Thanks.
  
  ------------------------------
  Harris Tse
  ------------------------------
  - ChayceDuncan
    Qrew Captain
    7 months ago
    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
    ------------------------------

Forum Discussion

Autogenerating Number