Chronologically numbering child records based on date field, auto update upon modification or added/deleted record

[ehwalker]

Hi,

I have a parent table "Animals" and a child "Exams". I would like to chronologically number each Exam for a given animal based on Exams::date (i.e. first exam = 1, second = 2, etc.) and I need this number as a STORED value. Additionally, as exams are added, I need this number to update across all child records for that animal in case an exam is added 'between' two existing records (eg. Exam 1 = 1Jan21, Exam 2 = 31Jan21.... new exam added as 20Jan21, so would need the previously labelled Exam 2 to become Exam 3, etc.).

I can do this with  calculation field no problem with a self join relationship from Exams... but I do need the value stored.

I can update an individual Exam # with a trigger field referenced in the autoenter calculation, but this doesn't update the number for the other Exam records for a given animal.

Is there a robust way to accomplish this?

Thanks.

Edited January 29, 2021 by ehwalker

[comment]

31 minutes ago, ehwalker said:

Is there a robust way to accomplish this?

 I tend to think not. Certainly not in a multi-user scenario where some of the records that need to be renumbered can be locked by other users. Even with a single user there are so many ways this could go wrong, it's not worth the effort.

OTOH, it is very easy to number the records dynamically - so the question is why do you need this value to be stored?

 

[ehwalker]

Thanks comment, I suspected this might have been the case. Thanks for the feedback.

I wanted to have the values stored so that they could be used in relationships to records in other child tables of a given animal. So, using this number to relate the first exam of an animal with the first 'capture' of an animal, or the first 'release' of an animal. From the 'Animals' table there are several child tables and sometimes I need these child records to be related to one another... I thought that numbering them somehow would make this simple, but perhaps it's best to think of a different approach?

[comment]

I am afraid I don't follow this. Is there a meaningful connection between say the 3rd exam of an animal and the 3rd release of same animal - even when the numbering can change as a result of deleting the 2nd exam?

I have a hunch you might benefit from having a single child table record all events related to an animal.

 

7 minutes ago, comment said:

Is there a meaningful connection between say the 3rd exam of an animal and the 3rd release of same animal - even when the numbering can change as a result of deleting the 2nd exam?

I should add that even if the answer is yes, there is a way to accomplish this without requiring all records to be renumbered after any creation/modification/deletion of a record.

 

Edited January 29, 2021 by comment

[ehwalker]

Thanks comment. Hmm, yes after considering your thoughts I think I have created separate tables where it would be better to have only had a single table.... it'll be a lot to rework this, but would also solve several other issues I've been having. Thanks for the thoughts.

Hmm, yes there are meaningful connections between these child records.... what would your suggested approach be to create these relationships?

[comment]

1 hour ago, ehwalker said:

yes there are meaningful connections between these child records.

Are you sure about this? You gave us very little background, but still I remain doubtful. I suspect the meaningful connection is based on chronology, not on order. IOW, for a capture that occurred on Jan 15, 2020, the meaningful release would be the first one that occurred on or after the same day - and this would be very easy to detect using a (sorted) relationship that compares the dates.

Anyway, in the interest of promoting knowledge, here is how you could establish a relationship matching on ordinal number. Let's continue with the example of 2 child tables: Captures and Releases. You already know how to calculate the ordinal number of a capture using a self-join relationship. Now, if you continue this calculation with:

GetValue ( List ( Releases::ReleaseID ) ; n )

where n is the calculated ordinal number of the current capture, you will have calculated the value of the primary key of the release whose ordinal number is equal to n - and now you can use this value to establish a relationship to another TO of the Releases table.

In actual implementation, the number of required TOs and fields could be reduced by keeping a list of child IDs in the parent record and looking up from there. Also, if each child table had a unique prefix to its ID, you could use a single calculation field to generate a list of all IDs of the records in other tables having the same ordinal number.

 

1 hour ago, ehwalker said:

I think I have created separate tables where it would be better to have only had a single table.... it'll be a lot to rework this, but would also solve several other issues I've been having.

Yes, poor structure often has many symptoms. I would encourage you to solve the problem at its root, instead of adding local fixes.

 

[ehwalker]

4 hours ago, comment said:

Are you sure about this? You gave us very little background, but still I remain doubtful. I suspect the meaningful connection is based on chronology, not on order.

Yes, it is the chronology that is important. For example, to calculate how long an animal has been in captivity, would need subtract the capture date from the subsequent release date. If I have a single table, then this sorted relationship would allow me to find the date of the next release by sorting the relationship and taking the first related value?

In regards to design and this overarching question: I have three main child tables of Animals: captures, releases, and holdings. I have join tables sitting between each of these and Animals as each 'capture', 'release' or 'holding' record can involve multiple animals. Considering your suggestion I'm thinking that I leave the main child tables as they are currently, but use a single table for the joins between each. This seems to solve my original question because I can perform these calculations within that single table? And it seems to address several other issues (e.g. creating a clear 'history' of what happened to each animal over the course of its life). Am I on the right track here?

 

5 hours ago, comment said:

Now, if you continue this calculation with:

GetValue ( List ( Releases::ReleaseID ) ; n )

where n is the calculated ordinal number of the current capture, you will have calculated the value of the primary key of the release whose ordinal number is equal to n - and now you can use this value to establish a relationship to another TO of the Releases table.

 

 

Thanks so much for taking the time to address this. So this relationship would be valid even though it's based on an unstored calculation? I hadn't considered calculating the primary key of the other record... very useful! 
 

5 hours ago, comment said:

In actual implementation, the number of required TOs and fields could be reduced by keeping of child IDs in the parent record and looking up from there. Also, if each child table had a unique prefix to its ID, you could use a single calculation field to generate a list of all IDs of the records in other tables having the same ordinal number.

Hmm, how does this lookup from the list of ids in the parent record work? I don't quite understand yet.

[comment]

5 hours ago, ehwalker said:

So this relationship would be valid even though it's based on an unstored calculation?

It would be valid, albeit in one direction only. In the given example, the "calling" record in Captures would "see" the corresponding record in Releases. The record in Releases would have to calculate the ID of the corresponding record in Captures in order to have a relationship that works in the opposite direction.

 

6 hours ago, ehwalker said:

how does this lookup from the list of ids in the parent record work?

I don't want to sidetrack this thread. Roughly, if the parent record has an unstored calculation of =

List ( Captures::CapturesID )

then each record in Captures can calculate its ordinal number in this list using:

ValueCount ( Left ( Animals::CaptureIDs ; Position ( ¶ & Animals::CaptureIDs & ¶ ; ¶ & CaptureID & ¶ ; 1 ; 1 ) ) )

 

[ehwalker]

Okay thanks so much, I've got it! Really appreciate your time and assistance here Comment!