Forum Discussion

Brian_P's avatar
Brian_P
Qrew Member
30 days ago

I need help creating a snapshot field in a unique parent-child relationship.

In my app, there is a relationship between two tables, call them Table A and B. In table A, there is a column called batch date. For this field, we would like to create a new field based off of it called initial batch date in the same table. This would essentially be the first value that was inputted in the batch date field (that field has end users inputting a value often). From the research I have done, the best way to preserve original values is using a snapshot field. From what I understand all I have to do is add a lookup field to the child table, then create a new field for a snapshot that effectively references this lookup field. The problem is in this relationship, table A is the parent and table B is the child. How would I create a snapshot field for table A in this situation? So far I added the lookup field in table B, then I created the snapshot there, and then added it as a summary field in table A to reference the original snapshot. This did not seem to give the desired result. Any suggestions? Open to anything. Sorry if it is not super clear as I am only a few months experienced in QB. Thanks,

Relationships
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    29 days ago

    Brian,

    This is a common problem.   Here is a solution for you using a reverse relationship.

     

    Make table Batch Date. Allow users to create records there but not edit anything.

    Create a Summary field between the Lab Test and Batch Date field to give you the Minimum value of the Record ID# in the Batch Date table.   By definition, that is the first one created.

    Create a new Parent Child relationship between the Batch Date and Lab Test with Batch Date as the Parent.  Make sure you use the Min RID Batch as the reference field when you create the relation ship.  Do not let it create a new field.

    Now in the new relationship make Date a look up field in the Lab Test table.   Change the name to something like First Date/ Original Date what ever makes sense for your lab.   You can now use that Date in all your forms and reports and it will not change.

    You can also learn more about that Date, such as who put it in, [Record Owner],  the date and time it was set  [Date Created] and so on.