Jump to content

Adding field from related table NOT working?


ron G

This topic is 4749 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am using FM 11 Adv

I have this relationship:

VisitorTO.jpg

I have 2 layouts:

VisitorDetail.jpg

This layout collects detailed visitor information. It works.

I have a Meetings Layout based On MEETINGS:: that collects the names of Members and Visitors. Visitor names are collected via a valuelist based on Visitors::Name_Last. This works. (see .jpg below)

MeetingAttendance.jpg

The problem is: "If I put any field (Like VISITORS::TITLE) from VISITORS on the VISITORMEETING JOIN portal, the field will not reflect the value that it has in ViSITORS.

It seems like this should work. But it doesn't...?

Coincidentally, GTRR from the "VisitorMeeting Join::Name" portal record to Visitors also does not work; it does nothing. I suspect the relationship is screwed up but I don't see how...

Here is an update: When a record is created in VISITORS, PK_Visitor of course gets a value BUT, it's related field, VISITORMEETING JOIN::KF_Visitor DOES NOT get that value... HUH????

Link to comment
Share on other sites

The MemberMeeting Join TO is not in the first image. What are its relationships?

Thanks for the reply.

The Members side is working. But, here is the ENTIRE relationship.

ishot-617.jpg

The problem is that both KF in "VisitorMeeting Join" don't get their corresponding Primary Key values.

What am I missing? Isn't FM supposed to automatically put the KP value in the KF field?

Link to comment
Share on other sites

If I put any field (Like VISITORS::TITLE) from VISITORS on the VISITORMEETING JOIN portal, the field will not reflect the value that it has in ViSITORS.

Most probably because it is displaying the value from the first related record, which is not the one you expect. So it could be an issue of context.

What are you using for the primary key values? I hope they are auto-emtered serial numbers and not names.

What TO is used for the layout in the third image above?

Link to comment
Share on other sites

Most probably because it is displaying the value from the first related record, which is not the one you expect. So it could be an issue of context.

What are you using for the primary key values? I hope they are auto-emtered serial numbers and not names.

What TO is used for the layout in the third image above?

This TO is based on Meetings.

The Portal on the left is based on MemberMeeting Join

The Portal on the right is based on VisitorMeeting Join

The 'problem' is that the PK in Visitors is NOT brought over to FK_Visitor; despite the 1 to many relationship. This means that visitors are 'invisible' to VisitorMeeting Join.

I DID solve the problem by scripting it:

ishot-618.jpg

But, I suspect this 'work around' will mean 'lot' of extra work as I start creating reports. Unless I am wrong, "Isn't FM, in a one-to-many relationship supposed to automatically bring the PK value over to FK????

Link to comment
Share on other sites

I DID solve the problem by scripting it:

But, I suspect this 'work around' will mean 'lot' of extra work as I start creating reports. Unless I am wrong, "Isn't FM, in a one-to-many relationship supposed to automatically bring the PK value over to FK????

No workaround required. No script required. Download the file in the previous post. There is no problem.

visitors.png

Link to comment
Share on other sites

There is no problem; it works fine as is.

MembershipXX.fp7.zip

WOW! This is what I expected.

I noticed you added Allow Creation Of Record ... on Visitors side.

And I noticed that the FK values are now working. But, I notice that when I select a different 'row' in the "VisitorMeeting Join" portal, the corresponding FK values do NOT change. (Obviously I am confused :D ) Shouldn't they?

ishot-620.jpg

Can you please elaborate your logical narrative as to what is going on and why this works? It would surely help me in 'getting' this lesson.

Thank you verrrrrrrry much for shedding the 'light'.

Link to comment
Share on other sites

WOW! This is what I expected.

I noticed you added Allow Creation Of Record ... on Visitors side.

And I noticed that the FK values are now working. But, I notice that when I select a different 'row' in the "VisitorMeeting Join" portal, the corresponding FK values do NOT change. (Obviously I am confused :D ) Shouldn't they?

I don't understand the question. There is nothing special here. This is an extremely basic implementation of a join table; and you put the join table in the relationship diagram yourself and chose appropriate foreign key fields. There is no way for me to imagine what part of this you don't understand so you will have to do some further explaining.

Link to comment
Share on other sites

But, I notice that when I select a different 'row' in the "VisitorMeeting Join" portal, the corresponding FK values do NOT change. (Obviously I am confused ) Shouldn't they?

I have no idea what that means; and you provide no description of what the picture is supposed to represent.

Different rows of the portal DO have different values for kf_visitorID.

Link to comment
Share on other sites

I don't understand the question. There is nothing special here. This is an extremely basic implementation of a join table; and you put the join table in the relationship diagram yourself and chose appropriate foreign key fields. There is no way for me to imagine what part of this you don't understand so you will have to do some further explaining.

Thanks for the reply.

I noticed that in your 'fix' you added 'Allow Creation ... in Visitors and Deletion in VisitorMeeting Join. I can clearly see that what you did worked. I don't know why it worked? Yes, it is pretty basic stuff; 1-to-many setups. You did something. I just don't know what.

By way of my continuing lack of understanding, I now am trying to get a date field stored in the Candidates2 table. It is basically the same problem as before but because I don't know how to 'fix' it, it happened again.

ishot-627.jpg

MembershipXXZ.fp7.zip

I am trying to get the Candidates2:Date_Bylaws onto my layout which is based on MEMBERMEETING JOIN. Again, MemberMeeting Join doesn't know about Members2:KP_MemberID so the 'request' for Candidates 2 information goes nowhere.

So, the question is: "How do I get MEMBERS2 PK_MemberID to be visible to "MemberMeeting Join"? Connecting them into a one-to-many relationship doesn't work... but souldn't it? But I know it should.

Link to comment
Share on other sites

Once again, there is no problem. Enter a date. Done.

It is impossible to help you if you are not going to provide detail and answer the questions. You posted a picture last time with no description of its source. You said something about "selecting a row" but again, no description. Go back to that problem. What exactly do you mean?

Link to comment
Share on other sites

Once again, there is no problem. Enter a date. Done.

Oh, you mean the Candidate2::Date field. I am NOT trying to enter a date. I just want to see on the layout the value that is currently in the Candidate2::Date field. As you can see, that field is blank on the layout despite the fact that there are values in the Candidate2::date field.

You say there is 'no problem' yet from my experience 'there IS a problem'. (see posted .zip filemaker file)

Under the category of "a picture is worth 1000 words" I will elaborate for you.

1) the relationships are as pictured. Do you need me to explain them? If so, just say so and I will.

2) As stated in the picture, the layout is based on "MemberMeeting Join"

3) All the Members 2 and Candidates 2 table fields show as blank (see picture. They are light blue and labeled)

4) It seems that the relationship (one to many) between MembersMeeting Join and Members 2 would mandate that the FK in MembersMeeting Join would be a link to Members. It doesn't seem to be. So, the question is "why not"?

Given your participation on this forum as a means to 'show' what you know and then solicit paid assistance if a user wants elaboration; I don't expect further help.

But, in any event, I hope this clears up my apparent obfuscation.

It is impossible to help you if you are not going to provide detail and answer the questions. You posted a picture last time with no description of its source. You said something about "selecting a row" but again, no description. Go back to that problem. What exactly do you mean?

Link to comment
Share on other sites

If I was in this situation with a client, I'd tell them to do their job (define the problem) and stop trying to do mine (find the solution). For a moment, think hard and define the problem you want to solve, and tell us what that is. All I can see in this post is bits of solution that don't work.

Given your participation on this forum as a means to 'show' what you know and then solicit paid assistance if a user wants elaboration...

I think you sound explain how you drew that conclusion. I have seen nothing in this thread that suggests this.

Link to comment
Share on other sites

You say there is 'no problem' yet from my experience 'there IS a problem'. (see posted .zip filemaker file)

I took a look at your file (not an easy task!). AFAICT, the problem you experience is one of data, not of relationships. No record in Candidates is related to a record in Members, because there is no data in the KF_MemberID field of the Candidates table.

I would also suggest you remove all the excess fields from your table. For example, if the relationship between Members and Candidates is based on:

Members::KP_MemberID = Candidates::KF_MemberID

then a field Members::KF_Candidates makes no sense. Neither does the MemberName field in MemberMeetingJoin. These are trees that obscure the forest.

Having a "developer" layout for each table, where all fields of the table (and some related ones) are shown would also help - if not you, then me.

Link to comment
Share on other sites

If I was in this situation with a client, I'd tell them to do their job (define the problem) and stop trying to do mine (find the solution). For a moment, think hard and define the problem you want to solve, and tell us what that is. All I can see in this post is bits of solution that don't work.

I think you sound explain how you drew that conclusion. I have seen nothing in this thread that suggests this.

Right, it was a 'private' communication. I am not offended (I believe in the profit motive). But, my project is a Membership database for a non-profit. Since I don't get paid I can't afford to pay anyone else. Just being 'shown' that something works has a great deal of value in itself. So, the time and effort to even do that are appreciated.

The problem I want to solve is that I want to 'read' the Contacts2::date_bylaws from the layout based on "MemberMeeting Join".

Link to comment
Share on other sites

Who approached whom?

And your interest in this 'matter' is...?

The bottom line for me is that I don't care if someone wants to 'show a solution' and then say they won't tell me how they got the solution because they don't work for free.... I appreciate knowing that a solution is possible. And, I would hire the solution consultant if I was not working on a non-profit/uncompensated job. There are a lot of good people on this forum who I can see contribute a lot of their time to helping others 'come along' in their understanding. A few 'stinkers' don't ruin that experience for me. :B

I took a look at your file (not an easy task!). AFAICT, the problem you experience is one of data, not of relationships. No record in Candidates is related to a record in Members, because there is no data in the KF_MemberID field of the Candidates table.

This is exactly the problem! Because there is KF value I can not access the Candidates 2:: Date_Bylaws field.

I keep asking this question but never get an answer: "When a one-to-many relationship is established doesn't that mean that FM will put the PK field in the FK in the Many field? (I thought it did but perhaps I am mistaken.) I've been reading a *lot* of FM books trying to get at why this doesn't take place but so far, no author, or contributor to this forum knows or is willing to tell.

As for the unneeded fields, I am aware of them but haven't gone to housekeeping yet. You see, I started this project over a year ago and got stuck. Spent a lot of time reading and getting better at development (still not quite good enough). I have changed the original design and added features... hence the seemingly unnecessary fields.

I would also suggest you remove all the excess fields from your table. For example, if the relationship between Members and Candidates is based on:

Members::KP_MemberID = Candidates::KF_MemberID

then a field Members::KF_Candidates makes no sense. Neither does the MemberName field in MemberMeetingJoin. These are trees that obscure the forest. Right, FK_Candidates was a 'test' at a relationship that did not work.

I agree that there are a lot of 'trees' but in the case of MemberName, it is a field that gets is input from a valuelist based on members that brings in the name of a member who attends a particular monthly meeting. I think it is necessary.

Having a "developer" layout for each table, where all fields of the table (and some related ones) are shown would also help - if not you, then me.

I will clean up the tables and create a layout with all the fields. Or, you can see them on the above MembersxxZ.fp7.zip

Link to comment
Share on other sites

And your interest in this 'matter' is...?

You tell me: you say it was a private communication - but you brought it out into public view. And when you publicly attack another forum member, I feel it's my responsibility as a fellow member to not let it pass.

Link to comment
Share on other sites

You tell me: you say it was a private communication - but you brought it out into public view. And when you publicly attack another forum member, I feel it's my responsibility as a fellow member to not let it pass.

..."attack"???? No attack intended from me. I just said what happened. Why is this such a big deal? Consultant helped me up to the point that he wanted to help me. That is his choice. It is his choice to essentially say "...beyond this I need to be paid." I don't have a problem with this. It seems like you do and I don't see why? He was open and honest with me. You still haven't explained why this is any of your business.

Link to comment
Share on other sites

..."attack"???? No attack intended from me. I just said what happened. Why is this such a big deal? Consultant helped me up to the point that he wanted to help me. That is his choice. It is his choice to essentially say "...beyond this I need to be paid." I don't have a problem with this. It seems like you do and I don't see why? He was open and honest with me. You still haven't explained why this is any of your business.

I think you have misrepresented the situation.

I made no offer of work for you. I have requested no payment. I have held back no information

What I DID suggest is that it is your responsibility to do your homework.

It is your responsibility to come to the discussion prepared. It is your responsibility to spend time and effort in careful review of things like details of relation graph differences.

In exactly this vein, it is not helpful for you to dismiss requests to substantially clean up your files before asking other people to spend their time analyzing them.

Link to comment
Share on other sites

This is a public forum. If you think something is "not my business", don't bring it up here. Especially if it's something that was said to you in private.

Why is this such a big deal? Consultant helped me up to the point that he wanted to help me. That is his choice. It is his choice to essentially say "...beyond this I need to be paid." I don't have a problem with this.

I don't have a problem with this either. But that's not at all what you said earlier:

Given your participation on this forum as a means to 'show' what you know and then solicit paid assistance if a user wants elaboration; I don't expect further help.

With all good will, I don't see a way to interpret this other than as an attack. A baseless attack, I should say, since by your own admission he did provide a solution to your problem. "Provide" - in an unlocked file, open for your inspection. Not "show" as means to solicit paid assistance.

Link to comment
Share on other sites

Got it to work!!

For the benefit of anyone else who runs into a similar problem, here is my solution:

Below is the reltionship diagram:

ishot-629.jpg

Here is the layout (Based on Meetings)

ishot-631.jpg

The problem was that I could not get the FK entered in either of the two JOIN tables. And, I was using a dropdown list to grab the NAME of the Visitor or Member from their respective tables. But, doing this mean there was no FK and therefore no way to access any other field in either Members2 or Visitors.

The solution:

Looking at the "Meeting Attendance" layout (Above--based on Meetings), I put the KF_Member field in the portal and then referenced a 'dropdown' value list that looks like this:

MEMBER LIST:

Fields: Members2::KP_MemberID and Members2::Name_LastFirst. (show only from 2nd value)

This means that the user 'selects' the name and that is what gets shown in the portal. But, the KP_MemberID of the selected member gets inserted into MemberMeeting Join::KF_Member field. Ba boom. Connected.

Did the same thing on the Visitor side.

Thanks to ALL who gave any kind of advice or made an observation. Hope this helps someone else.

Link to comment
Share on other sites

This topic is 4749 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.