Table lookup from child to parent

[Schorner]

Goal: To fill in the child form from the family form when there is no entry in the child record.

I have a contact table with phone and email.

Today, every one in the family has their own cell phone number.

Thus I made a family member table with name, phone and email.

I complete the contact table first and there is a browse within that form for the family members

As you enter a family member, it is looking up the key and entering the same key in the family member record. The user enters the name of the family member. I'd like to copy the phone and email from the contact table subject to override in the family member table.

I made a field rule for the key field in the family member record. It presently does fill in the key. I have also told it to fill in familymemberphone from contactphone if isblank("familymemberphone") with or without the condition, I don't get the phone number or the email copied.

Similarly, I have an actor table which lists the shows that the family members were in. It has fields, Last (key), First, M_F, Child, LastAudition, LastCast, Show
I am getting the drop down for show name and date to work just fine, one picks from the various shows.
I'd like to get the First field to drop down a list of the family members with the same last name so that the user can chose which family member and it will automatically fill in the first, M-F and Child from the familymember table. My drop down now shows all familymembers from all families. I have the set defined so that there is a one to one link from actors to familymembers and the link is based on the mutual key Last. How do I get the drop down to show only the familymembers with the same Last key?

Thanks for your help
Jim
Vero Beach Theatre Guild

[Mike Wilson]

Re: Table lookup from child to parent

Jim,
Welcome to Alpha. We are here to help as best we can. The more specific and detailed the information provided...the better a response can be. A posted example of your application with good instructions is always the best way to get folks on the boards to be able to help more efficiently.

From what I read - Parent table = "Contact" and Child table = "Family" The linking field wasn't given.

Goal: To fill in the child form from the family form when there is no entry in the child record.

I believe you are meaning "table" and not "form", and you do not have separate forms to enter records for the Contact table and a separate form for adding records to the Family table. This seems to be corroborated with...

I complete the contact table first and there is a browse within that form for the family members

So your using a single form based on the Contact-Family set.

I made a field rule for the key field in the family member record. It presently does fill in the key.

I will take "key" to mean the set linking field .... the unique record number that links the parent record to the child record. You can't place a field rule on the linking field in the child table. This field is mastered in the parent table. Placing a field rule on the field in the child table is problematic.

As you enter a family member, it is looking up the key and entering the same key in the family member record.

It is a standard, background feature for Alpha to fill in the linking field value in the child table when entering the child record on a form based on the parent-child set. That happens without any user coding. Coding to "make this happen" will cause conflicts.

I have also told it to fill in familymemberphone from contactphone if isblank("familymemberphone") with or without the condition, I don't get the phone number or the email copied.

Need to explain what "told it to" represents in more specific detail.

Similarly, I have an actor table which lists the shows that the family members were in. It has fields, Last (key), First, M_F, Child, LastAudition, LastCast,
Show

Poor choice for a linking field. Not unique enough. All the Smith families will be linked together.

I'd like to get the First field to drop down a list of the family members with the same last name so that the user can chose which family member and it will automatically fill in the first, M-F and Child from the familymember table. My drop down now shows all familymembers from all families.

Use a table look up field rule pointed to the family table with a filter in the look up definition.

[Schorner]

Re: Table lookup from child to parent

Jim,
Welcome to Alpha. We are here to help as best we can. The more specific and detailed the information provided...the better a response can be. A posted example of your application with good instructions is always the best way to get folks on the boards to be able to help more efficiently.

From what I read - Parent table = "Contact" and Child table = "Family" The linking field wasn't given.

Jim: The linking field is called Last. Last is in fact unique in that it is the last name of the contact plus the first initial if necessary.

I believe you are meaning "table" and not "form", and you do not have separate forms to enter records for the Contact table and a separate form for adding records to the Family table. This seems to be corroborated with...

Jim: I see my confusion. I have prepared a form for the user to enter the information in the table. The form contains the spaces to enter the information in both tables. The child table is a browse within the parent form. Thus, indeed I do not have separate forms for each. I'd be happy to send you what I've started but for now envision the form with the contact info, primary name and address and phone number. E.g. Mr. Mike Wilson, PO Box 123, Santa Fe NM 90102 (701)245-1235. Below is a browse with the family members. Your record is first, Mike Wilson, and your phone number is the default, no over-ride is required. Next, your wife, Jane Wilson, and her phone (701)245-1236 is typed over the default. Your son, Jason, is 3 years old and doesn't have a phone yet. When I enter Jason Wilson, I'd like your phone number, from the contact (parent) record to be entered without my transcribing it.

So your using a single form based on the Contact-Family set.

I will take "key" to mean the set linking field .... the unique record number that links the parent record to the child record. You can't place a field rule on the linking field in the child table. This field is mastered in the parent table. Placing a field rule on the field in the child table is problematic.

It is a standard, background feature for Alpha to fill in the linking field value in the child table when entering the child record on a form based on the parent-child set. That happens without any user coding. Coding to "make this happen" will cause conflicts.

Jim: Yes, key is the set linking field, Last. I now understand why part works and the other doesn't.

Need to explain what "told it to" represents in more specific detail.

Jim: "Told it to" means that I've entered instructions in the Table Lookup of the child table on Last to Fill fields of the child table with data from the parent table. I recognize that this does not work but don't know why or how to fix it. Why is not critical but how to accomplish this goal would be very helpful.

Poor choice for a linking field. Not unique enough. All the Smith families will be linked together. I've uniqued the Smiths to be SmithJ and SmithR

Use a table look up field rule pointed to the family table with a filter in the look up definition.

Jim: Is the filter: Family->Last = Child->Last?

I really appreciate your help. Please pardon my vocabulary challenges, my first language is accounting, I'm learning database speak.

[Mike Wilson]

Re: Table lookup from child to parent

I would have said place this on the child table Lastname Event Field Rule OnWroteField

parentform:browse1:familymemberphone.value=parentform.contactphone.value

but you do than and the phone field disappears. I tried it with v10 and v11. The value fills in but the browse field vanishes. Clearly a bug!

Later:
I made a new database for an example of the problem and the problem was not there, and that strategy of that code on the OnWroteField field rule worked fine. I did the above in AlphaSport, so something else is happening that creates the problem. For your use Jim it should work.

[Schorner]

Re: Table lookup from child to parent

I'd be happy to try it. I take it that this is in the Code tab (which I've never used). I guess I click New and then which of the drop down items and what next?

Jim

[Mike Wilson]

Re: Table lookup from child to parent

Nope. Table Field rule.
Family table in design mode, chose Field rules.
Far right tab is Events
Select the Lastname field
mark field event radio button
Chose event OnWroteField
write the following in the editor: parentform:browse1:familymemberphone.value=parentform:contactphone.value
Save
see if it works.

[Schorner]

Re: Table lookup from child to parent

Your instructions were very clear. When I completed them and went to the form to try, upon reaching the end of a new record the circle whirled but no phone number was entered. If you'll email me, [email protected], I'll share the file with you on Google Drive and you can see what I did and try it yourself.

Thanks,

Jim

[Mike Wilson]

Re: Table lookup from child to parent

Jim,
You can zip your file and send it or post it on this board for others to see. The latter is typically done as example with no sensitive information. If you wish to not have to take time to remove sensitive info and send it just to me, zip the file and email it to me. To zip the file go to the Control Panel, select the Table/Set tab, chose the Tools menu option, and then Zip database. This will place a zipped copy in the folder of the app. You can send it to me at [email protected].

[Schorner]

Re: Table lookup from child to parent

Jim,
You can zip your file and send it or post it on this board for others to see. The latter is typically done as example with no sensitive information. If you wish to not have to take time to remove sensitive info and send it just to me, zip the file and email it to me. To zip the file go to the Control Panel, select the Table/Set tab, chose the Tools menu option, and then Zip database. This will place a zipped copy in the folder of the app. You can send it to me at [email protected].

[Schorner]

Re: Table lookup from child to parent

I got the first part figured out with help from Tom but we still have the second part to conquer:

I'd like to get the First field to drop down a list of the family members with the same last name so that the user can chose which family member and it will automatically fill in the first, M-F and Child from the familymember table. My drop down now shows all familymembers from all families.

Thanks for your help.

Jim