It is currently 25 September 2020, 2:44 Advanced search

Borrowed, calculated fields from parent tables in document

Questions and answers on how to best use Instant Developer

Borrowed, calculated fields from parent tables in document

Postby ATroyer » 21 November 2019, 15:46

Question from a newbie:

I have three tables/documents, two parents and one child:

1. School, containing SchoolID, SchoolAbbreviation, etc
2. Person, containing PersonID, PersonFullName, etc
3. SchoolPerson, which has lookup forms referencing School and Person and borrowed properties from both parent documents.

I have a field populated by a calculation in SchoolPerson (Name) that combines PersonFullName + ", " + SchoolAbbreviation during the document event OnValidate.

Two problems:
1. When I save a document, the borrowed fields in SchoolPerson (PersonFullName and SchoolAbbreviation) do not populate until I refresh the row. I would like to see them immediately on change(On Validate, Reason=1) or on saving (OnValidate, Reason=0).
2. The calculated field SchoolPerson Name never populates until I save the new row, update the row with changes, and save again. I know I have the borrowed and calculated fields set up correctly, because they do work...eventually.

From what I am seeing, the SchoolPerson document is not getting the properties from the parent tables/documents when needed.

I am able to get things to work when I use EndModal events on the SchoolPerson form, but I would like to work completely within the DO structure if at all possible, to make the code more reusable in the future.
Besides, I discovered that EndModal events do not fire if I type directly in the lookup field instead of clicking the field to access the lookup form.
I tried using other document events, such as BeforeSave, AfterSave, OnInserting, AfterInserting, etc.

How do I get the child document to access the relevant properties in the parent documents in a timely fashion?
Posts: 12
Joined: 30 October 2019, 13:30

Re: Borrowed, calculated fields from parent tables in docume

Postby ljwilson » 24 January 2020, 17:57

I've attached a sample project with a simple data structure (Access MDB table) based on your description.

3 Tables:
Schools (id/name/abbreviation)
Persons (id/firstname/lastname/fullname)
Schools Persons (id/person name school name/school id/ person id). Child of Schools and Child of Persons.

  1. Persons Table: "fullname" field is calculated from firstname last name using DO.
  2. Schools Persons table: "person name school name" field is calculated from school parent and person parent using DO.
  3. For both calculations I used the respective classes' On End Transaction Event (DO). I chose On End Transaction rather than On Validate since it comes sooner in the process, and the help mentions On End Transaction is preferred if you are working with fields that depend on other field's values:
    This event should be used to analyze the changes made to the document and respond appropriately, for example by setting default values, performing calculations, and updating other related documents. However, it should not be used to validate the changes or report errors. For that, the OnValidate event should be used.
  4. For the lookups I've got form lookups combined with drop-down's so you have the best of both worlds if wanted.
  5. I made the id's visible on the detail forms when adding/editing so you can see them changing in action.

There is a lot in this project--would be a good subject for a webinar on getting the "blue fields" created the way you want them. InDe will create some automatically if the relationships are correct, but sometimes (like in this case) you need to add more lookup fields or change how existing ones are linked and I haven't found how to do that documented very well.

Calculated Child
InDe 19.5 r12 Express
(476.59 KiB) Downloaded 82 times
Posts: 533
Joined: 26 November 2013, 14:15

Re: Borrowed, calculated fields from parent tables in docume

Postby ATroyer » 15 February 2020, 3:13

Works perfectly, thanks!
Posts: 12
Joined: 30 October 2019, 13:30

Return to Tips & Tricks

Who is online

Users browsing this forum: No registered users and 0 guests