(Using FileMaker Pro v21.x)

Happy holiday season to all:

For reasons my boss wants (for our FMP ordering system), she wants records using the same product type to be sequentially ordered in each order no matter how many of the same item is added or deleted before the final "File Order" button is clicked, e.g., COPY-01, COPY-02, COPY-03, COPY-04, etc. It makes sense since there are subparts to each item so it makes it easier for the Billing team to reference all the charges to COPY-01, all the charges to COPY-02, etc.

I suppose I could code a script that would fire every time an item is added or subtracted from the order so it would renumber the items sequentially without leaving any "holes" in their numbering scheme, but out of curiosity I was wondering if I could go the calculation route, instead. For grins, I used Perplexity to create a WHILE calculation and it came up with this:

While (  

[

i = 1;

target = x;    

foundCount = Get(FoundCount);    

result = ""  

];  

i ≤ foundCount and IsEmpty(result);  

[    

currentRecord = GetNthRecord(yourField; i);    

result = Case(i = target; currentRecord; "");    

i = i + 1

  ];

  result

) 

 

...but I just can't get it to work despite any field I choose for Target and yourField. (Admittedly, I'm having trouble understanding how the calculation works), so is it a bad calculation or am I missing something with the fields I choose for the calc?

 

Cheers,

Rich

 

Is it safe to assume the context is an Order with a portal to LineItems?

If so, I would do something like the attached.
 

Re your calculation, I don't understand the logic it attempts to implement. And I don't think it can be done by calculation alone. 

Depending on the format you need this, there may be a much simpler alternative: sort your line items by ProductID and use a summary field that counts them with restart. This will work if you print your orders from the LineItems (as you should), as well as in a sorted portal.

 

 

SimilarChildrenNumerator.fmp12

I'll check it out--thanks!