Count existing entries in a form with a specific criteria and return value of new entry by +1

Count existing entries in a form with a specific criteria and return value of new entry by +1

I am currently working on a way to generate codes for items. I have two main questions concerning my application, which are:

- How can I have zoho automatically number based on existing records and a specific criteria
- How can zoho automatically display a code which is based on another field AND a count function

Let me explain in detail...

My application has two forms: Items & Product Code Generator


In a first step I will add an item to the Items form. The form has three fields which are Item Name, Category and Abbreviation.

Let's say I add a new item to the Items form, which is an apple. I enter apple as the item name, I select "Fruit" from a dropdown for the category and specify an abbreviation such as APL. I now submit this entry.

In a second step I select the product code generator form and start generating a code for this product. I do this by selecting my item (lookup from Item form) from a dropdown list. I would like the Code to be composed of the abbreviation "APL" followed by a three digit number (e.g. APL-001).

Now I would like to learn how to have zoho automatically display this code in a new field called "item code" in the product code generator form.


The final step would be to have zoho automatically number items based on the abbreviation given. Let's say I would want to add a second apple now. In the product code generator form I would like zoho to automatically number the second apple based on how many items already exist in the product code generator form with the code "APL".



The second apple should have the code APL-002. However should I now add the first banana to my product code generator I would like the code to be something like BAN-001 (of course I first will have to add the information to the Items form in a first step).

I hope you understand my problem. I have been giving this a lot of thought and it's probably not complicated at all, but since I am new to zoho creator it's been quite a challenge for me.

Many thanks!!!!