Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago

Determining Next Item Number

I have attached a screenshot showing a dynamic form rule to determine my next item number for my meeting minutes.  Basically what I am doing is taking a summary field of the # of meeting minute items related to the meeting minute.  So I get a total count of the items in each category.

I did a lookup to the summary field.

Now that I enter this I can assign the item number to the "last" item.  My 7th entry is 6 because I cannot figure out where to place a "+1" as a formula.  I thought maybe a formula field but it is continuously updating ,not just once.  So not sure how to either lock that, or I need to use this method.

Only other thing I can think of is to create another set of fields that are formula fields and increment the number.  Just seems ridiculous to have to create (5) additional fields to add (1).

Formulas and functions
Tables and fields
  • XavierFan's avatar
    XavierFan
    Qrew Cadet
    6 years ago
    If I understand you correctly - you already have:

    1)  Summary of # of Meeting Minute Items (related to the Meeting Minute)
    2)  Lookup to the summary field - call it [Current # of Meeting Minute Items]

    My suggestion would be:

    - Create a Formula Numeric field called [Next Item Number]- in the Meeting Minute Item table.

    Formula would be:

    [Current # of Meeting Minute Items} + 1

    e.g. If you have [Current # of Meeting Minute Items] is 6, then [Next Item Number] would be 7

    So say you have a Numeric [Item Number] field in your Meeting Minute Item table.

    Then in your form rule - you can say:

    If [Item Number] is blank

    change [Item Number] to the value in the field [Next Item Number]

    And uncheck the box that says "Fire "change" actions only when a condition changes from false to trueonly change this when this becomes true".

    Then this form rule would always apply when the [Item Number] is blank.


    This will work as long as you're only adding Meeting Minute Items, and not deleting items in the middle, etc.  But you can always Grid Edit the Meeting Minute Items and update the [Item Number] if you need to re-order the items.

    Let me know if this helps.
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      That is basically what I thought

      I am creating item numbers based on the categories.  I want each category to keep its own tally of item numbers.

      So basically under 1. Construction I have item 1, 2, 3, 4, etc.

      Under 2. Permit I have another set of 1, 2, 3, 4, etc.

      Both could have an item number 1, because they are in different categories.

      So I need to create (5) formula fields for the next item number.


      Just one more question that comes of this based on your response.  I was trying to use the "blank" or null determination on Item Number and was having trouble.  I was saying if Item equals and leaving that box empty.  Assuming that means blank.  Is that correct or do I actually write the word blank in there?
    • XavierFan's avatar
      XavierFan
      Qrew Cadet
      6 years ago
      So yes - you would need to keep track of the "next" number for each Category separately.

      If you had Category has its own table - then you'd use only 1 set of lookup / formula fields (the trade off being you have to set up this new table, etc.)

      [Iten Number] = the value (leaving this blank) - this should work in the form rule.

      That's assuming that [Item Number] is not being defaulted to 0 or some other value, etc.
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      So all working now.  I just ended up defaulting the item number to 0 and checked to see "if it is 0" and if it is we change it on save.