Automatic code creation

[ArsGoetia]

Hi everyone,

I've been using Filemaker for quite sometime, but I'm somewhat unexperienced when it comes to scripting.

Imagine you have 3 fields, the first two use value lists. I'm struggling to create a script that will create a code with an incorporated counter on the third field. I exemplify:

Imagine that in the first field, I have the following options:

Orange (OR)

Apple (AP)

In the second, I have the following options:

Red (RD)

Blue (BL)

 

If I chose Orange and Blue, i want that the third field should appear as OR-BL-001 (separated by an hyphen). Each time I choose the same combination of options, the field contents should be incremented by one (OR-BL-002, OR-BL-003, and so on).

Any help?

Thanks in advance!

Edited October 20, 2020 by ArsGoetia
Mistake

[comment]

6 hours ago, ArsGoetia said:

Each time I choose the same combination of options, the field contents should be incremented by one

That is a very problematic requirement, because (if I understand correctly) the result would depend on other records. How exactly it should depend on them is not clear: suppose you already have two records with the same combination AP + BL, so the first record is tagged as "AP-BL-001" and the other as "AP-BL-002". Now what do you expect should happen if:

• the first record is modified to another combination;
• the first record is deleted;
• the first record is omitted from the found set;
• the records are sorted in reverse order;
• another existing record is modified to the same combination.

So the real question here is why do you need this (or think you need this)?

 

Edited October 20, 2020 by comment

[ArsGoetia]

First of all, thanks!

 

If a record is modified or deleted, it should - if possible - run a script that verifies all the (previous and following) combinations and correct them. I don't know if it's feasible. I would like to implement this (and variations of this) for applying reference codes in a small archive.

[comment]

40 minutes ago, ArsGoetia said:

run a script that verifies all the (previous and following) combinations and correct them

Corrects them to what exactly? You are using terms that do not have an exact definition (as I tried to illustrate earlier). There is no "previous" and "following" combination unless you also specify the found set and sort order.

I would ask again why you need this, because - as you may have gathered by now - this is by no means simple. Except for one scenario: in a report sorted by the combination of the two fields, you can easily number sequentially the records of each sorted group by using a summary field. Such numbering would be produced "on-the-fly" without being stored in a field.

 

Edited October 20, 2020 by comment

[ArsGoetia]

Yes, you're right. By "previous" and "following", i was referring to the same combination of the deleted record and the same combination of the newly modified record, using the record number sort order from the earliest record to the most recent. If we have OR-BL-001  and OR-BL-002, and OR-BL-001 is deleted, OR-BL-002 should become OR-BL-001.

Using a summary field would allow me to use the 3 digits (001) that I intended?

[comment]

4 minutes ago, ArsGoetia said:

Using a summary field would allow me to use the 3 digits (001) that I intended?

You would need to also use a calculation field =

SerialIncrement ( "001" ; sCount )

 

[ArsGoetia]

12 minutes ago, comment said:

You would need to also use a calculation field =

SerialIncrement ( "001" ; sCount )

 

Thanks! Just one more last question: I've been having some trouble in numbering a report sorted by the combination of the two fields with a summary field. Can you help me in this?

[comment]

See if the attached demo helps:

demo.fmp12