Adding a counter to a value from parent table and saving in child table.

Question

I have two tables, Projects and Samples. Samples is a child to Projects. One to Many Samples per Project. 
I want to populate a field in a Samples Record with a number from the Project (Project ID) and add to it the following (-01) going up incrementally by 1 whenever I add another sample.
Example:
Project:Project ID = 22220000
Samples: Sample Number: 22220000-01
If I come back later and add another sample it would be 22220000-02, and so on. The counter starts at 01 if we are in a new project adding related samples. I hope I'm explaining it properly.  ------------------------------Andrew Konopacki------------------------------

RaziD · Answer

You need to create summary field in Project table(number of Samples), lets say you name it Counter
Bring Counter field as a lookup into Samples table ==>Project - Counter
Create numeric field in Samples Table ==> Counter(SS)
In field (Counter(SS)) settings under Advanced section change it to as in image(below)
Your Sample Number formula needs to be as below

I haven't try it. If [Counter(SS)] =0 for first Sample Record you can add 1 in formula ==>NotLeft(ToText(101+[Counter(SS)]), 1)
I hope this help.
Thanks

------------------------------
Razi D.
Desta Tech LLC
razi@destatechs.com
------------------------------

Sign up

Login to the community