Portal records don't match the table it was based on

[4Justme2]

My portal records don't match the table it was based on. The ID_PAR.pk is incorrect in the portal (the table records are generating correctly). I have four related tables in all. HOUSEHOLDS (the parent), PARTICIPANTS, PARTICIPANTS_2_CHILDREN and CHILDREN. They are related as follows:

HOUSEHOLD::ID_HHD.pk is linked to PARTICIPANTS::ID_HHD.fk

PARTICIPANTS::ID_PAR.pk is linked to PARTICIPANTS_2_CHILDREN::ID_PAR.fk

CHILDREN::ID_CHI.pk is linked to PARTICIPANTS_2_CHILDREN::ID_CHI.fk

I have a portal on the PARTICIPANTS layout to the CHILDREN records allowing me to create new CHILDREN records from within the portal. The join table, PARTICIPANTS_2_CHILDREN, is capturing the three the key fields from HOUSEHOLD, PARTICIPANTS and CHILDREN. My script reads as follows:

Freeze Window

Set Variable[$ID_PAR.pk;Value;PARTICIPANTS::ID_PAR.pk]

SetVariable[$ID‑HHD.fk;Value;PARTICIPANTS::ID_HHD.fk]

Go to Layout [“CHILDREN_TABLE”(CHILDREN)]

New Record/Request

Set Variable [$ID_CHI.pk; Value; CHILDREN::CHI.pk]

Go to Layout[“PARTICIPANTS_2_CHILDREN_Table”(PARTICIPANT_2‑CHILDREN)]

New Record/Request

Set Field [PARTICIPANT_2_CHILDREN::ID_PAR.fk;$ID_PAR.pk]

Set Field [PARTICIPANT_2_CHILDREN::ID_HHD.fk; $ID_HHD.fk]

Set Field[PARTICIPANT_2_CHILDREN::ID_CHI.fk;$ID_CHI.PK]

Go to Layout [original layout]

Go to Field [select/perform; CHILDREN::Name_First]

The table, PARTICIPANTS_2_CHILDREN is capturing the records correctly, creating three foreign keys per record (ID_HHID.fk; ID_PAR.fk; and ID_CHI.fk) for the three tables (HOUSEHOLD, PARTICIPANTS and CHILDREN).

However, I have a portal based on the PARTICIPANTS_2_CHILDREN table... this portal is on the HOUSEHOLD table. The portal data is only showing one of the ID_PAR.fk's when there are multiple ID_PAR.fk's for a single house. I have no filter or sort on the portal. I’m wondering if maybe I need a loop in my script generating the new records and if so what would that look like? or maybe my problem has nothing to do with my script since me data is generating correctly in the table—just not showing correctly on the portal. Any ideas?

[bcooney]

I don't understand your data model. Households consist of People, perhaps each with a type. What are you modeling?

[LaRetta]

  On 3/11/2012 at 8:29 PM, 4Justme2 said:

My portal records don't match the table it was based on. The ID_PAR.pk is incorrect in the portal (the table records are generating correctly). The portal data is only showing one of the ID_PAR.fk's when there are multiple ID_PAR.fk's for a single house.

Have you checked that the fields inside the portal are from same table occurrence name as the portal? If that isn't it then we would need to see a zipped, empty clone of the file - like Barbara, I don't understand your structure to properly advise further, sorry.

[4Justme2]

VOL Clone.zip

I had to make a change to the structure of my database when I realized I should be assigning children to households, first; before assigning relationships between those children and the adult (participant) members in the same household. My recent change to the db structure didn't resolve my problem, though.

I'm attaching a clone of the db. The db works as follows: First the household is created using the Household_form; then a child is created using the Children_form; at which time the child is assigned one or many households. Then Participants (same as a parents) are created using the Participants_form. It's at this juncture I run into a problem with my portal. My portal should display children living in the same household as the partiicpant record; which it does, but it should also allow the enduser to create a relationship between the particpant and child (mother, father, step father...) and that information populate a different table (which is my problem). When I try to change the relationship between on one participant record, it makes the same change on any other participant records that particular child appears on. My problem is the portal is displaying records at the household level--as it should, but I want a different table to capture the unique relationship between the current participant record and the child living in the same household. I'm really stumped here. Any assistance you could give you be greatly appreciated. Thank you for taking the time to look at this... mp

[LaRetta]

Hi 4JustMe2,

I can't get past this question ... why are Participants (parents) in separate table than Children? What happens when a child has a child? They are all people and I would have them in the same table. Each person belongs to a household table and then the join table (Relationships?) is used to specify a role of each person to another.

  Quote

to capture the unique relationship between the current participant record and the child living in the same household.

To capture the unique relationship from whose perspective? The parent or the child?

If all people are in a People table and you use a join, you can include each person's role to the other in the join as well. So Person ID 1 is parent Joe Smith and Person ID 10 is child Joey Smith. The join table would create two records:

Record1

Person_1_ID = 1

Relationship = Father

Person_2_ID = 10

Record2

Person_1_ID = 10

Relationship = Child

Person_2_ID = 1

With this setup, you can search for all 'fathers' in the join to send Father's Day cards even if a father is actually a child of someone else. This also provides the unique perspective I believe you are looking for ... on a participant's record, it will show the Household people. And those that are related will also display their 'role' to the current person (participant).

Added blue for clarity

Edited March 17, 2012 by LaRetta

[4Justme2]

In regard to the relationship, I'm only interested in the relationship of the participant to the child (so mother, father, step father, etc). I do a lot of reporting on participants only and/or children only in addition to households data.

You're right, of course... they are all people. I originally had them in the same table. But a child may live in multiple households and each parent (participant) may have multiple children and each child will have multiple parents. It can be a one-to-many from both directions (either from the parent or the child). It is a problem for me when the child themselves becomes a parent or when they reach the age of adjulthood (18 or older)... I thought I'd address this by automatically deactivating the child record and forcing the enduser to recreate the record in the participant table. I know it's duplicate entry which I am trying to avoid but I don't understand how get around it.

Am I going about this wrong way or is there another way I'm not thinking of?

If I went with the scenario mentioned above where...

Record1

Person_1_ID = 1

Relationship = Father

Person_2_ID = 10

Record2

Person_1_ID = 10

Relationship = Child

Person_2_ID = 1

To create this particular scenario would it simply mean just a household table, a people table and a joint table? I don't know how I would link back to myself to create the relationship between person 1 and person 2.

But definitely want to do this the right way even if it means some re-work on my end. What would you do if you were me? Would you put them all in the same table? I hired help at one point and that person put children and participants in two different tables. Up until that point I was stuck around this issue and couldn't seem to move forward. Now, although I'm finally moving forward it sounds like I may be going about this the wrong way. It boils down to this relationship I can't seem to get my arms around between participants and children. Is the attached database way off from how this should be done?

[LaRetta]

  Quote

I'm only interested in the relationship of the participant to the child (so mother, father, step father, etc.

It is a problem for me when the child themselves becomes a parent or when they reach the age of adjulthood

Well, you give all people Roles or keep track of their ages and assign them Roles when they turn of age.

  Quote

I'd address this by automatically deactivating the child record and forcing the enduser to recreate the record in the participant table.

Do ANYTHING but this. Relationships happen between two people. I might have gone a bit deeper than needed but see how this fits. Notice the grey Person~related table occurrence is the People table as well. No moving records because People are people and they change and so do their relationships. That is why we have a Relationships join table. And People are people and they move around (and live in multiple places simultaneously) and that is why we have a Household_People join table.

I don't know if this is the best way but that is why we share ideas on this forum. Only once we know the structure is correct should we even consider how to display it for a User. We will help you through it - I have never created a genealogy solution so I may be due for surprises as well.

Households.zip

[4Justme2]

I really appreciate that you took the time to go into this detail... that's what I've lacking and causing me so much confusion around how to go about this correctly. I've spent an embararrasing amount of time grueling over how to correctly set up these parent/child relationships. I am extremely grateful you took the time to get down to this level of detail for me in the attached Household file. That’s so terribly helpful. As a result, I have questions.

It looks like the cListRElative calculation simply concatenates the word “is” with the relationship. That’s the one calculation I understand. What do these others do?

cMyRelations

cHouseholds

cRelatives

These calcs are unstored because relationships are fluid, changing… I get that. But with these people stored all in the same table how would you suggest I go about tracking these changes. For example, instead of being a parent/child relationship if were a guardian/child relationship it might be important for me to know when that person became the guardian of the child or when someone else because the guardian instead. For example, on 1/6/12 Tom was the guardian of Mary. On 1/6/13 Mary’s guardian changed to Bill. Maybe that’s just a matter of adding a date in the join table?

In addition, the demo table looks like it's a self-join of the cartesian join type, do I understand that correctly? If so, I'm surprised that the link is between name and ID... two completely different types of fields, one containing a number and the other text. HI wouldn't have thought you could create a join like that. How does that work?

Also, with the parents/children in separate tables it was easy for me to take the end-user to a layout where they could view all parents or all children; and/or generate value lists of just children or parents. How would this be done, for example, in the attached Household database? I tried this originally back when I had the people all combined into one table. There are two ways we distinguish between Participants and Children. Participants are either over 18 or they are under the age of 18 but a parent, themselves. The database contained the Age field, and I added a second field titled Parental_Status which had a Boolean value 1=parent. But I couldn’t figure out how to isolate this criteria of records for the end-user when the people combined into one table. What would you suggest? If I do this with a script based on a Find and title the layout page Children… but then, after the fact, they could change their find criteria and end up with parents on the same layout… it would confusing and inaccurate.

Lastly, I just want to reiterate how much I appreciate the time you have put into helping me with this. Thank you.

[LaRetta]

  Quote

It looks like the cListRElative calculation simply concatenates the word “is” with the relationship. That’s the one calculation I understand. What do these others do?

cMyRelations (not used yet). I established it because it would then show all relationships that a person might be involved in (on either side). But it would be used for displaying results and I was unsure what you wanted so I stopped there. cHouseholds (List provides list of all related households that a Person is associated with). cRelatives (Lists all of the people related to this person). You can read up on the List() and Substitute() functions in your online Help to see what they do.

  Quote

These calcs are unstored because relationships are fluid, changing… I get that.

Not exactly. The calculations are unstored because they reference related fields. The relationships between people and where people live is fluid and changing so the join tables are required – to produce one record for each change in these relationships/households.

  Quote

… it might be important for me to know when that person became the guardian of the child or when someone else because the guardian instead. For example, on 1/6/12 Tom was the guardian of Mary. On 1/6/13 Mary’s guardian changed to Bill. Maybe that’s just a matter of adding a date in the join table?

Probably. A date field in Household_People can be used to track date when associated with that one location and a date field in Relationships can track when that relationship began (for biological mother it would be birthdate of child usually), or the date someone is granted Guardianship. You may not need to enter dates in all situations. For instance in Relationships, you may not need to enter a date when specifying Jesse as the brother of Sally – it would just be Jesse’s birth date. But if ‘son of step-mother’ then it might be important to list when Nancy became his step-mother.

  Quote

In addition, the demo table looks like it's a self-join of the cartesian join type, do I understand that correctly? If so, I'm surprised that the link is between name and ID... two completely different types of fields, one containing a number and the other text. HI wouldn't have thought you could create a join like that. How does that work?

Cartesian Product relates the current parent record (even 0 record number) to ALL records in the other table. The connection can use ANY fields between tables (even invalid ones) … all except summary and container. The fields can be empty and the parent table can even be empty (no records). I used it here only to display all the people and results in a single portal for the demo.

  Quote

Also, with the parents/children in separate tables it was easy for me to take the end-user to a layout where they could view all parents or all children; and/or generate value lists of just children or parents. How would this be done, for example, in the attached Household database?

Whether one table or two, the questions are the same and so are the relationships we establish. There is nothing two tables can do that one table can’t. The real question is … as this database grows and the people grow and change, how do you plan to keep track of children having children and how will this impact your structure? So a 17-year-old this year (a child) will become a Participant next year and you want them automatically to appear in a value list of Participants? Where is the BirthDate so this can be tracked?

This brings us back to the question asked by Michael (Comment) ... what is the purpose of this solution? Understanding that would help us help you.

A value list cannot be based upon an unstored calculation (looking for whether the Person has children and looking at their date of birth … so you must manually flag each record (using your Parental_Status field) if you want to create a value list of ‘parents’. But it will not necessarily remain accurate as kids have kids and people age. I created a conditional value list which pulls only those People flagged with Parental_Status so you now have a value list with only ‘participants’ in it. But keep in mind this will not remain accurate unless you keep that checkbox up-to-date.

  Quote

If I do this with a script based on a Find and title the layout page Children… but then, after the fact, they could change their find criteria and end up with parents on the same layout… it would confusing and inaccurate.

But that is true in all FM tables. This is why you must control User actions. You do NOT split data into different tables simply because you want to isolate the data being displayed in a find on a layout. So when you switch to the layout with Participant fields, use layout script trigger to perform the find for 1 in Parental_Status. And when you switch to layout and you only want child records, perform a find (again fired by layout script trigger) for 0 in the Parental_Status field.

I made a few changes to the file – moving Roles to a table, attached activity dates, added your flag field and value lists. I think you might want ability to identify if a role is parent, child or other so I added that Category. I am still unclear on some of your rules and the purpose but hopefully this will get you moving. :yep:

Households2.zip

[4Justme2]

You've covered everything so thoroughly. Unfortunately, I have been laid up with an unpleasant intestinal bug so I haven't been able to examine this as closely as I need to... but will make every attempt to do so tomorrow. In the interim, I thank you BIG TIME and will respond to your questions asap. :shocked:

[4Justme2]

  On 3/19/2012 at 4:43 PM, LaRetta said:

This brings us back to the question asked by Michael (Comment) ... what is the purpose of this solution? Understanding that would help us help you.

I need to track family dynamics along with (participant, children, household) involvement in programs, services and/or events.

Reporting needs:Compare family dynamics by quarter (i.e. Fall 2011, Spring 2012, Summer 2012, Winter 2012) Comparisons are based on the number of “active” people within each category (this could be by households, participants, Children; familial roles grandparents; age related identify such as child, teen, etc) Whether a person is “active” is identified by the last time the person was involved in any of the programs, services and/or attended events within the past 18 months. When all of these changes occur is important to me since I’m comparing the dynamics from one quarter to the next. In addition, I also keep track of Staff and their professional development activities and degrees, as well as Contacts and addresses of Affiliate companies. Of course, I don't track the same information for Staff and Affiliate Contacts as I do or Participants (ie I don't need age type of information on affiliates, I don't need addresses on staff (although I guess I could put that data in here) but I do on Affiliate contacts; I do need to track which events staff participate in, etc.). How would you recommend I handle these particular categories of people. Would you still recommend that I add them to the People table even though I track different information on them? I could identify them as Staff or Affiliates through Roles; however, in the current setup, in order to get to Roles you have to through Relationships; and they won't exist in the table since the relationships are based on relationship to children. Does that make any sense? Also, I need to track when certain fields change such as Martial Status, Parental Status, Disabled Status and Name Change; all fields contained in the People table. What do you suggest would be the best method for me to track the dates of these particular transactions?

  On 3/19/2012 at 4:43 PM, LaRetta said:

Whether one table or two, the questions are the same and so are the relationships we establish. There is nothing two tables can do that one table can’t. The real question is … as this database grows and the people grow and change, how do you plan to keep track of children having children and how will this impact your structure? So a 17-year-old this year (a child) will become a Participant next year and you want them automatically to appear in a value list of Participants?

A person is considered a "Participant" if either one of two conditions is true. If the Parental Status is true (even if they are only 13) they are no longer considered a child... they are a "Participant". OR if they are over 17 years of age they are automatically considered a "Participant". What is the best way for me to handle this in the database? Can I create a field to populate automatically based on the either of the criteria above so I don't have to rely on the end-user to manually identify this category?

  On 3/19/2012 at 4:43 PM, LaRetta said:

Where is the BirthDate so this can be tracked?

The BirthDate field is in the Participant table. Unfortunately, we aren't always able to get their birthday from them. It's a sensitive environment. We could be dealing with people who are living in extreme poverty and/or abusive environments and they are often reluctant to give up information. We really want them to participate so we'll take whatever we can get. We can usually get their age, but not always their birthday. If we are provided with a BirthDate I have a calculation that automatically applies the age to the Age field. If we are only given an age, I have a calculation the logs the date the age was provided; and another that will add 1 to the age a year from the this date. So at least, we have a ball-park idea of the person's age. It's not that important to us that the age is 100% accurate... again, we'll take what-ever information they are willing to provide us with and try and work with that.

  On 3/19/2012 at 4:43 PM, LaRetta said:

A value list cannot be based upon an unstored calculation (looking for whether the Person has children and looking at their date of birth … so you must manually flag each record (using your Parental_Status field) if you want to create a value list of ‘parents’. But it will not necessarily remain accurate as kids have kids and people age. I created a conditional value list which pulls only those People flagged with Parental_Status so you now have a value list with only ‘participants’ in it. But keep in mind this will not remain accurate unless you keep that checkbox up-to-date.

This solution will work nicely. Thank you.

  On 3/19/2012 at 4:43 PM, LaRetta said:

But that is true in all FM tables. This is why you must control User actions. You do NOT split data into different tables simply because you want to isolate the data being displayed in a find on a layout. So when you switch to the layout with Participant fields, use layout script trigger to perform the find for 1 in Parental_Status. And when you switch to layout and you only want child records, perform a find (again fired by layout script trigger) for 0 in the ParentalLayout trigger TRG: OnRecordLoad sets PeopleID in Households and Roles_Status field.

This will work nicely as well. However, I have a question... I'm looking at the layout setup and don't see the script trigger on the layout. Am I looking in the wrong place?

  On 3/19/2012 at 4:43 PM, LaRetta said:

I made a few changes to the file – moving Roles to a table, attached activity dates, added your flag field and value lists. I think you might want ability to identify if a role is parent, child or other so I added that Category. I am still unclear on some of your rules and the purpose but hopefully this will get you moving.

This is a tremendous help in getting me to understand and helping me to move forward. I am extremely appreciative of your help. I'm just now getting to look at this in more detail as I've been laid up with an illness that was very persistent and knocked me off my feet. I thank you, so much, for taking the time to address all of my issues and concerns in such great detail.

[4Justme2]

Another question, I'm assigning people to households through a portal which works great on the People layout. I'd like to be able to remove people when they no longer live in the household... but I don't want to delete the history of that activity (that they once lived there). I need to know that, say Sally, lived on Spruce Street beginning whatever date, no longer lives on Spruce as of whatever date.How would I do that? If I delete the record from the portal row it appears to delete the record from the table the portal is drawn on.

[LaRetta]

Well, Sally can actually live in two households at once, can't she - when custody is split? The Action date indicates when she 'began to be associated' with a household. If, from the people table, you have a portal based upon HouseholdPeople, then you can sort HouseholdPeople descending on Action Date and the first related Household record will be the most current.

Your other option is to include HouseHoldPeopleID in your People table. Placing the primary 'many' ID into a parent is used when specifying a default address (from several in an addresses table) or preferred phone number when a client has many phone numbers in the Numbers table. In this way, you can specify ONE primary address for the child but I am not sure this is what you need.

Another option ... if it is true that a child can live in two households simultaneously then you may wish to include a flag field (number) called IsCurrent in your HouseholdPeople table. 1 would indicate still current and 0 would indicate inactive. Then filter your portal with: HouseHoldPeople::IsCurrent. This is probably the best choice for your needs since it allows multiple active addresses while deactivating old addresses.

added blue

Edited March 24, 2012 by LaRetta

[4Justme2]

  On 3/24/2012 at 3:13 PM, LaRetta said:

Well, Sally can actually live in two households at once, can't she - when custody is split? The Action date indicates when she 'began to be associated' with a household. If, from the people table, you have a portal based upon HouseholdPeople, then you can sort HouseholdPeople descending on Action Date and the first related Household record will be the most current.

Yes, children can live in multiple households at the same time and I need to track that. However, in this particular scenario I was thinking more along the lines of when a family moves from one location to another. I believe your last suggestion below would work well under these particular circumstances.

  On 3/24/2012 at 3:13 PM, LaRetta said:

Your other option is to include HouseHoldPeopleID in your People table. Placing the primary 'many' ID into a parent is used when specifying a default address (from several in an addresses table) or preferred phone number when a client has many phone numbers in the Numbers table. In this way, you can specify ONE primary address for the child but I am not sure this is what you need.

This would be a great way for me to identify the primary caregiver address. I'll try it out.

  On 3/24/2012 at 3:13 PM, LaRetta said:

Another option ... if it is true that a child can live in two households simultaneously then you may wish to include a flag field (number) called IsCurrent in your HouseholdPeople table. 1 would indicate still current and 0 would indicate inactive. Then filter your portal with: HouseHoldPeople::IsCurrent. This is probably the best choice for your needs since it allows multiple active addresses while deactivating old addresses.

I could definitely make use of this solution for families when they move from home physical location to another. I didn't realize you could filter your portal on a field value. Once you pick field, where do you enter the value of "1" ?

green added

[LaRetta]

Hi 4JustMeTo,

Placing one of the 'child' IDs into a parent works very well. I usually rename it. For instance, if it is for the relationship and you create the RelationshipID but name it PrimaryRelationshipID ... or CareGiverID so you know its purpose, especially if you want two RelationshipIDs for two different purposes. Add a comment on the field so you know it is actually the RelationshipID.

  Quote

Once you pick field, where do you enter the value of "1" ?

You simply double-click and insert the field into the calculation. Portal filtering is based upon Boolean logic. The flag field you create (number) will hold only a 1 or 0. Its value tells the filter to include or exclude it. And with Boolean, any number returns true so the same would work to filter a date (only include records with a date). Just select the date field. If there is ANY date, its value is 1 or greater so it will work the same.

[4Justme2]

  On 3/24/2012 at 3:13 PM, LaRetta said:

A value list cannot be based upon an unstored calculation (looking for whether the Person has children and looking at their date of birth … so you must manually flag each record (using your Parental_Status field) if you want to create a value list of ‘parents’. But it will not necessarily remain accurate as kids have kids and people age. I created a conditional value list which pulls only those People flagged with Parental_Status so you now have a value list with only ‘participants’ in it. But keep in mind this will not remain accurate unless you keep that checkbox up-to-date.

My problem here is that not all participants are parents. They could the be a childless live-in boyfriend or girlfriend of the parent and still be considered a participant in the program. Both the parental status and the age have to be taken into consideration when determining if someone is a "participant". If either of the conditions below are true the person is considered a participant.

If age > 17 = Participant Status is true OR

if parental status is true = participant (regardless of age)

So maybe it's an additional "participant status flag" that I need? Can I automate updating the "participant Status" to 1 automatically if age is >17? That would take care of a portion of the criteria for identifying if someone is a participant.

[4Justme2]

  On 3/24/2012 at 5:41 PM, LaRetta said:

Placing one of the 'child' IDs into a parent works very well. I usually rename it. For instance, if it is for the relationship and you create the RelationshipID but name it PrimaryRelationshipID ... or CareGiverID so you know its purpose, especially if you want two RelationshipIDs for two different purposes. Add a comment on the field so you know it is actually the RelationshipID.

Primary caregiver is maintained at the household level per child. So Child 1 and 2 both live in the Jones Household. But, for Child1 the Jones Household is the primary caregiver, for Child2 the Smith Household is the primary cargiver (even though both children reside at the Jones Household at least some of the time.). In this scenario, which ID would I place in which table?

[LaRetta]

If you have field ParentalStatus (number) and you have field Age (number), then create a new cParticipant field (result is number) with:

People::Age > 18 17 or ParentalStatus = 1

... this will produce Boolean true (1) and since it references two stored fields, it will not need to be unstored can be stored. If you wanted to have system keep up-to-date (using birth dates) or checking to see if they have children, then it would need to be unstored.

corrected age typo and corrected double negative

Edited March 24, 2012 by LaRetta

[LaRetta]

  On 3/24/2012 at 6:04 PM, 4Justme2 said:

Primary caregiver is maintained at the household level per child. In this scenario, which ID would I place in which table?

You want to identify a unique aspect of a child. For Child1 who lives in the Jones Household (ID1) and they are primary caregiver, you would want the Jones Household ID1 placed in Child1's record. For Child2, who lives at Jones Househol(ID1) AND Smith Household (ID2) but Smith Household is the caregiver then you want the Smith Household (ID2) to be placed in Child2's record.

... so you would place the HouseholdPeopleID in the People table.

[4Justme2]

  On 3/24/2012 at 6:06 PM, LaRetta said:

If you have field ParentalStatus (number) and you have field Age (number), then create a new cParticipant field (result is number) with:

People::Age > 18 17 or ParentalStatus = 1

... this will produce Boolean true (1) and since it references two stored fields, it will not need to be unstored can be stored. If you wanted to have system keep up-to-date (using birth dates) or checking to see if they have children, then it would need to be unstored.

Good catch on my typo, thanks. If I used an unstored field I would be wiping out my history each time the value changes, yes? Since I report on this changes by action date maybe I need to make it a stored value and sort my portal on the most recent action date(s)? I may be confusing myself a little here. Maybe when I actually try this the answer will be more obvious.

[LaRetta]

It was my typo, LOL.

  On 3/24/2012 at 6:36 PM, 4Justme2 said:

If I used an unstored field I would be wiping out my history each time the value changes, yes?

No. YOu'll see when you try it. Calculations are not data. Your data and records remain (and should never be deleted). A calculation just adds depth of information. Regardless, the calculation I gave you will be STORED (since you plan to manually mark if someone is a parent and enter their age) so it is safe to use. And it just FILTERS the portal as you need it.

[4Justme2]

  On 3/24/2012 at 6:12 PM, LaRetta said:

You want to identify a unique aspect of a child. For Child1 who lives in the Jones Household (ID1) and they are primary caregiver, you would want the Jones Household ID1 placed in Child1's record. For Child2, who lives at Jones Househol(ID1) AND Smith Household (ID2) but Smith Household is the caregiver then you want the Smith Household (ID2) to be placed in Child2's record.

... so you would place the HouseholdPeopleID in the People table.

Works like a charm! Many thanks... mp

[4Justme2]

  On 3/24/2012 at 6:06 PM, LaRetta said:

If you have field ParentalStatus (number) and you have field Age (number), then create a new cParticipant field (result is number) with:

People::Age > 18 17 or ParentalStatus = 1

... this will produce Boolean true (1) and since it references two stored fields, it will not need to be unstored can be stored. If you wanted to have system keep up-to-date (using birth dates) or checking to see if they have children, then it would need to be unstored.

corrected age typo and corrected double negative

I've done something wrong. I placed this Participant field on my layout but it populates as soon as I create a new record... and at the time I create a new record the parent status and age field is blank. My ParentalStatus is a number with auto calc: Case ( IsEmpty ( Self ) ; 0 ; Self ) (this is the checkbox that they fill in manually)

My Age field is simply a number

I created a participant calculation: Participant.c, PEOPLE::AGE.17 or PARENTALSTATUS=1, unstored to re-evaluate.

Any idea what I might have done wrong?

[LaRetta]

Make sure you create a calculation and not number with auto-enter calculation.

  Quote

PEOPLE::AGE.17 or PARENTALSTATUS=1, unstored to re-evaluate.

PEOPLE::AGE.17 ... is this the name of the field? You want field > 17 so if field name is PEOPLE::AGE.17, it should read:

PEOPLE::AGE.17 > 17 or PARENTALSTATUS=1

And do NOT set the calculation to unstored. I didn't explain well enough ... since both fields referenced in the calculation contain regular stored data in the same record (age and parental status), the result of the calculation can also be stored. It will re-evaluate automatically whenever a value changes in either Age or Parental_Status. :^)

[LaRetta]

WHOA! You have PEOPLE::AGE.17 > 17 or PARENTALSTATUS=1

Your calc should look like this: Parental_Status = 1 or Age > 17

Since your calc displays the table occurrence before the field name (separated by two colons), it suggests it comes from a different table. The People table should hold the Parental_Status AND age field also. Is that how you have it?

Added blue

Edited March 24, 2012 by LaRetta

[4Justme2]

  On 3/24/2012 at 8:14 PM, LaRetta said:

Make sure you create a calculation and not number with auto-enter calculation.

PEOPLE::AGE.17 ... is this the name of the field? You want field > 17 so if field name is PEOPLE::AGE.17, it should read:

PEOPLE::AGE.17 > 17 or PARENTALSTATUS=1

And do NOT set the calculation to unstored. I didn't explain well enough ... since both fields referenced in the calculation contain regular stored data in the same record (age and parental status), the result of the calculation can also be stored. It will re-evaluate automatically whenever a value changes in either Age or Parental_Status. :^)

Excellent! Works Beautifully. Awesome! :yep:

[4Justme2]

  On 3/24/2012 at 8:20 PM, LaRetta said:

WHOA! You have PEOPLE::AGE.17 > 17 or PARENTALSTATUS=1

Your calc should look like this: Parental_Status = 1 or Age > 17

Since your calc displays the table occurrence before the field name (separated by two colons), it suggests it comes from a different table. The People table should hold the Parental_Status AND age field also. Is that how you have it?

Added blue

Now that this is working... I'm wondering how I can do two things:

1) Is there a way I can show text based on the value in this field (0 = Child, 1 = Participant) to make it obvious to endusers? Would I do that by creating another field, a calc maybe?

2) How can I apply a action date to this field change? When these people are first entered they are either a participant or a child; a date should be associated with that transaction which would also correspond to the creation date. But when field is changed from a Child to a Participant, that transaction date if important to me. How could I automate a such a date?

[LaRetta]

  Quote

1) Is there a way I can show text based on the value in this field (0 = Child, 1 = Participant) to make it obvious to endusers? Would I do that by creating another field, a calc maybe?

Using a number result was an example. You can make your Participant.c calculation return a text result instead:

Case (Parental_Status = 1 or Age > 17 ; "Participant" ; "Child" )

Usually I suggest Boolean because 1) number is the smallest stored resource, 2) you can use number formatting on a layout to name the 0s and 1s however you wish even changing them depending upon layout, or using only one or the other (although there is a 7-character limit unfortunately), 3) number also uses least resource in relationships, when setting fields in scripts, index size etc and 4) number is fastest in searches and sorts.

However, if a field is a primary identifier (and it can't be handled within 7 characters with number formatting as in 'Participant') then you might wish to consider making Participant.c return text. My reasoning is this:

If (just-about) every layout, report, export in your solution needs these words then it is worth the resource to create the calculation (which is stored). Otherwise you will need to stack fields or use merge variables to provide the alternate display and on every layout, you will again have to remember or notate on the layout that you have stacked fields or merge variables, and you will probably eventually want to export those words as part of a Manager's excel sales sheet anyway so so just create the calc as text now. Sometimes a single calculation such as this IS worth creating. But make sure it is one you really will use. I think this one would quality. But if the words only appear for a few layouts (and you cannot use number formatting), stack the fields (we can point to some links about how to do it).

  Quote

2) How can I apply an action date to this field change? ... But when field is changed from a Child to a Participant, that transaction date if important to me.

Do you want to track only when Parent_Status is changed? Either direction, such as if User makes a mistake? Do you want to track when the age is changed? If you wish to track multiple dates against a single record then it suggests a one-to-many (1:n) meaning create a related table. But it would depend upon WHY it is important. Is it just to view in case someone has a question on when it was changed last and then changed again? Is it for a report? Will you need to search on these multiple dates or do you need to also track who made the change? You will need to work through these rules. Luckily with good structure, adding later requirements is piece of cake.

ADDED: It seems that I am going against what I usually suggest but I'm not. These are two words that will not later be changed and there are only two - one or the other. If multiple 'words' or wording might change then I would not likely make this suggestion.

Edited March 24, 2012 by LaRetta

[4Justme2]

  On 3/24/2012 at 10:29 PM, LaRetta said:

However, if a field is a primary identifier (and it can't be handled within 7 characters with number formatting as in 'Participant') then you might wish to consider making Participant.c return text. My reasoning is this: If (just-about) every layout, report, export in your solution needs these words then it is worth the resource to create the calculation (which is stored).

Your hunch is correct. This is a key identifier in almost all of my reports. With that in mind, I'll go ahead and change it to text.

  On 3/24/2012 at 10:29 PM, LaRetta said:

Do you want to track only when Parent_Status is changed? Either direction, such as if User makes a mistake? Do you want to track when the age is changed? If you wish to track multiple dates against a single record then it suggests a one-to-many (1:n) meaning create a related table. But it would depend upon WHY it is important. Is it just to view in case someone has a question on when it was changed last and then changed again? Is it for a report? Will you need to search on these multiple dates or do you need to also track who made the change? You will need to work through these rules. Luckily with good structure, adding later requirements is piece of cake.

Good questions. I'd rather not track it if they made a mistake but if there's not way around it I'd rather error on the side of tracking vs. not. The change I'm concerned about tracking is from the direction of Child to Participant. It should only happen once (once they are categorized as a participant they never go back to being a child).

However, there are several fields like this that play a key role in my overall solution. My reports are primarily done quarterly where I'm looking at the number of families during a particular quarter, number of participants, children, marital status number, age categories... all by what the history shows during a particular date range. Here's a list of the fields: ParticipantStatus, ParentStatus, Age (if we were not provided with a birthdate allowing us to auto calc to age), Marital Status, Disabled, Active(determined by whether or not they participated in any programs, utilized any services or attended any program sponsored events within the past 18 mos)... those are all from the People table. From the household table I need to report by date range on the number of families from a particular state or town during a specific period of time.There are additional fields as well that fall into this same category where I need to track, by date range, the number of families, participants, and children participating in programs, events or utilizing services. I'm not as concerned with who made the changes but it's info that may be information that is handy to have at some point down the line.

Given my needs, do you think I'd best served with a one -to-many related table? I can't even envision what that would look like given there are so many fields of data I need to track. It seems unlikely that I'd a table for each field, I hope. What would you suggest?

[LaRetta]

  Quote

by date range, the number of families, participants, and children participating in programs, events or utilizing services.

Then 'programs, events and services' should be tables and participants enrolled in the program, event or service. I honestly can't go into that much detail in a forum thread - these rules must be fleshed out into the various configurations and I could only guess. It seems we keep getting more and more requirements ... kinda like scope creep, LOL.

My best suggestion, now that you can study your existing relationships, is to draw out all of these rules and entities onto paper. Just start putting down all of the requirements and then organizing them into possible tables. Post here again with specific request.

I do not believe in a Dates table. Dates are not things. But when I have a set number of 'tasks' or I need to document date for multiple tasks on a client, for example, I will use an Activities table. I suggest you go that direction.

Added: Nothing wrong with scope creep either. And I know you can't produce all information in your opening thread. ;-)

Edited March 24, 2012 by LaRetta

[4Justme2]

  On 3/24/2012 at 11:44 PM, LaRetta said:

. My best suggestion, now that you can study your existing relationships, is to draw out all of these rules and entities onto paper. Just start putting down all of the requirements and then organizing them into possible tables.

I apologize for not better articulating my design requirements. I've mapped this out it seems a million different ways. Below are the tables and fields of data in my current solution. All fields in red font are fields I need to track history on (original date, original value, current value, current value date). As you will see, most of those fields are contained in the PEOPLE table. Most of the other tables have an action date that applies to the entire table history tracking is not a problem.

PEOPLE

ID_PEO.pk, Number, Indexed, Auto-enter Serial, Can’t Modify, Auto

ID_HHD.fk, Number, Indexed

ID_NOTP.fk, Number, Indexed

Name_Last, Text

Name_First, Text

Name_full.c, Calculation

Birthdate (not always provided)

AgeByBirthdate.c (calculates age if birthdate field is populated)

Age (when entered manually a date automatically populates the age modification date field

AgeModificationDate

NextSerialYear.c, calculates the next year the age needs to be changed

Disabled, 1=disabled

MaritalStatus, valuelist

ParentalStatus, 1=Parent

ParticipantStatus, 1=Participant, 0=Child (rule: if Age>17 or ParentStatus=1, “Participant”, else “Child”

ActiveStatus, Yes or No

Gender

Email

Phone1

Phone2

HOUSEHOLDS

ID_HHD.pk

Household Name

Street

City

State

Zip

FullAdress.c

Action Date

PEOPLE_2_HOUSEHOLDS

ID_H2P.pk

ID_HHD.fk

ID_PEO.fk

ActionDate

ActiveInThisHousehold

RELATIONSHIPS

ID_REL.pk

ID_ROL.fk

Person_1_ID

ActionDate

Person_2_ID

ListRelative.c

ROLES

ID_ROL.pk

ROLE

AFFILIATE_ORGANIZATIONS

ID_AFF.pk

Name

Contact

City

State

Zip

Postal

Phone1

Phone2

Email

Fax

REFERRALS_2_STAFF

ID_AFF.fk

ID_STF.fk

ID_REF.unique

Referral_Date

Referral_Type

ActionDate

STAFF

ID_STF.pk

Name_First

LDSName_Last

Name_Full.c

Title

PrimaryResponsibilities

Funding_Source

Funding_Percentage

Time_Allocation

Training_Degrees

TypicalWorkWeekHours

STAFF_2_RESOURCE_ALLOCATION

ID_REA.pk

ID_STF.fk

Comments

Funding_Source

Week of

Hours

TRAINING_2_STAFF

ID_TRA.pk

ID_STF.fk

ActionDate

Type

Description

EVENTS

ID_EVE.pk

ID_STA.fk

Description

Type

Venue

Event_Date

Start_Time

End_Time

Funding_Source

Funding_Percentage

Staff_Contact

EVENTS_2_PEOPLE

ID_PEO.fk

ID_EVE.fk

ID_ETP.unique

ActionDate

PROGRAMS

ID_PRO.pk

ID_STF.fk

Program_Name

Start_Date

End_Date

PROGRAMS_2_PARTICIPANS

ID_P2P.pk

ID_STF.fk

ID_PEO.fk

ActionDate

SERVICES

ID_SER.pk

ServiceType

Description

Location

SERVICES_2_PEOPLE

ID_S2P.pk

ID_STF.fk

ID_PEO.fk

ActionDate

  On 3/24/2012 at 11:44 PM, LaRetta said:

Post here again with specific request.

How do I track the history of changes (original value, original value date, current value, current value date) on the Name_Last, Age, Disabled, MaritalStatus,

ParentalStatus, ParticipantStatus,andActiveStatus, fields in the PEOPLE table?

[LaRetta]

Well, each of your join tables listed has an action date so they are (or should be) creating a new record for each combination change. That is how it should be handled. Only two tables (People and Staff) have fields which aren't set up to be tracked by join table.

Staff (Funding_Source, Funding_Percentage & Time_Allocation) will need a join table but you have Staff_2_ResourceAllocation so the date is should be there. It suggests this portion of your graph needs a bit more work - such as an ActivityDate in Staff_2_ResourceAllocation (mostly a guess). And in People table you have: Name_Last, Age, Disabled, MaritalStatus, ParentalStatus, ParticipantStatus,andActiveStatus. ParticipantStatus? If it is the calculation your created then you don't need to track it since you are tracking age and Parental Status (which combined create the calculation). I suggest you search for audit log or audit trail or check out these links:

http://www.nightwing...sX/demoX01.html

http://www.syncdek.com/fmDataGuard/

DataGuard is incredible, can cover an entire table/file with only 30-minute setup, and runs in the background with little footprint. In addition to providing audit tracking ability, it provides option to undelete every step back through time, ability to search (because it creates a standard FileMaker file with the changes), restore data incrementally (used for restoring all changes since last backup quickly) and the list goes on. If you cannot invest in DataGuard then Nightwing's audit log is good choice.

I do not know of anyone who has designed an audit log using script triggers. It sounds good in theory but it would be tricky using script, and also slow and potentially breakable.

[LaRetta]

ADDED: The idea is to use a join table to track changes where you need more than just the date a field changed (or you might need to search that date). But, for the life of me, I cannot understand why you would want to track the date when the Active status is changed from yes to no (one of your examples). Your solution is still fluid and you need to work a bit more on clarifying the rules. Don't track everything just because you think you might need it ... you will bloat your system with unnecessary fluff. Use join tables when you can and, in the case of People, I suppose you could use another table with dates and what field changed but ...

When you generate reports, the results should be based on the family dynamics at that time. I cannot imagine the need to generate a report which asks whether a child was disabled or a guardian was married six months or a year prior. Most reports want family status as of now.

[4Justme2]

  On 3/25/2012 at 12:42 PM, LaRetta said:

When you generate reports, the results should be based on the family dynamics at that time. I cannot imagine the need to generate a report which asks whether a child was disabled or a guardian was married six months or a year prior. Most reports want family status as of now.

I really do report this way, looking at these family dynamics in quarterly comparsions; including the number of disabled per family per quarter; and the number of particpants married by quarter, by age range, etc. The result looked at in this light can tell you a great deal about the social dynamics in a particular region.

Thank you for all of your time... I really chewed up a good chunk of your time and am very appreciative of your assistance. You've given me a lot ideas to work with here and it's making all the difference. Also, I feel much better about the db now that have combined the participant and child tables into one. Thank you again... mp

Just another thought on the audit trail. I bet I could generate a quarterly export, linked to an Access database, that will retain the history for me.