portal fields

[saralee]

Hi everyone,

This is another general question that I got stuck with.. I have been using portals in my layouts, and I noticed that the fields defined in the portals are not field contained in the related table where the portal is placed, as portals are just field holders. Am I right? I just wonder if I can somehow make those fields be part of the table, as I need to retrieve the content of those portal fields in the layout where the portal is placed. Hope it makes sense.

Thank you. :)

[bruceR]

No, you are not correct. A portal "points to" a particular table occurrence that you have selected; for instance Invoice Line Items. The fields in the portal must come from that table occurrence. (Actually they may also come from other table occurrences that are related in the graph.)

[saralee]

Ok, i got that, Bruce.. but can I somehow include those fields in the current table as well? because they are technically not part of the table, right? Can I somehow retrieve the information contained in the field in the current table? Thanks.

[bruceR]

A portal is a way of expressing and displaying something you have decided about your data. It is a way of selecting one thing and displaying the many things that you have decided are connected to it. As I understand it, you're doing something with babies.

You can select a particular baby; and see the many adults that are related. Or you can select a baby; and see the history of doctor appointments. Or select an adult; and see the list of their children. In each case the portal displays fields from the "many" side of the relationship that you have declared.

So in the case of baby => doctor appointments, the portal might show the date; and doctor name; and reason for the appointment. The layout you would be on would be based on the "Baby" table occurrence and the portal would come from "Appointments".

[bruceR]

Ok, i got that, Bruce.. but can I somehow include those fields in the current table as well? because they are technically not part of the table, right? Can I somehow retrieve the information contained in the field in the current table? Thanks.

What would be the point of that? You display the data from the current table outside the portal.

"technically not part of the table"? Not sure what you mean by that, which thing you are referring to. Can you give an example? A general way of discussing related data is to identify the "parent" table and the related "child" records. This can literally be a parent and child; or one of thing A has many of thing B.

[saralee]

Hi Bruce. Thank you for your example specific to my case Yes I got all of that. My issue now is that I have a household layout with a children portal set up properly, and I need to generate an email message including the parents and children's information. So I need to pull info from related fields in the household layout/table. And my question now is how to get all the children's information right from the household table. I do have a separate tblBabies which is related to tblHousehold, but the script triggered button which generates email msg is placed in the household layout.

I hope it make sense! :)

[bruceR]

Well; sort of. But the children's information is not in the household table. It's in tblBabies and that's where you pull it from. You have to create a calculated message for your email.

"Dear " & household::Firstname & ":¶¶" &

"You must be so proud of your babies " & substitute( List( tblBabies::FirstName); ¶; ", ") & "."

[saralee]

Right,, i'm using calculation to compose my msg, but the thing is I cannot retrieve all the children's information (household with more than one kid) by simply calling the baby name fields in the babies table... It doesn't seem like it is recognizing the household relationship at all..

[bruceR]

But that has nothing to do with portal fields.

Capturing related data is not a technical problem. If you have written inaccurate calculations, then somehow you'll need to learn how to fix that.

"It doesn't seem like it is recognizing the household relationship at all."

WHAT doesn't seem like it is recognizing? Please recognize that you have provided no meaningful information. You have basically said, "I'm trying something and it doesn't work. Can you guess what I did?"

An answer in the same vein then is:

Well, try something that DOES work.

Or better - provide meaningful information.

You'll probably need to upload your files or an example file, or at least your script.

Edited July 23, 2010 by Guest

[comment]

When you are in the parent table (Households), a reference to a field from the child table (Babies) returns data from the first related record.

In order to retrieve data from all related records, use the List() function as shown above.

[saralee]

Thank you both, BruceR and comment! I got the idea of using the reference list to put all related records, but for some reason, I could not pull more than one field in the list function. Here is my current calculated field. In the "children info" section, the script only outputs one kid's information, but not the siblings' info. Also, the related studies info (which is in another related table) does not show up at all..

Any ideas? Thank you!

"Parent(s) info: "&If (HouseHolds::DadFirstName ≠ "";HouseHolds::DadFirstName&" "&HouseHolds::DadLastName;"")&" "&If(HouseHolds::MomFirstName ≠ "";HouseHolds::MomFirstName&" "&HouseHolds::MomLastName;"")&";¶"

&"Phone number: "&If(HouseHolds::HomePhone ≠ "";"Home phone: "&HouseHolds::HomePhone;"; no home phone number available")&If(HouseHolds::CellPhone ≠ "";"Cell phone: "&HouseHolds::CellPhone;"; no cell phone number available") & ";¶"

& "Kid(s): " & Substitute( List( Babies::babyFirstName); ¶; ", ") & ".¶"

&"Child(ren) info: "&Substitute(List(Babies::babyFirstName;Babies::babyLastName;Babies::FullAge);¶; " ")& ".¶"

&"Studies related info: "&If (Studies::DateOfVisit ≥ Get(CurrentDate);Studies::DateOfVisit&" "&Studies::VisitTime&" "&Studies::StudyLab&" "&Studies::StudyName)

[comment]

The calculation should be in the Households table.

[bruceR]

The list function is great, but has some limitations. This won't work:

& "Child(ren) info: " & Substitute(List( Babies::babyFirstName; Babies ::babyLastName; Babies::FullAge);¶; " ") & ".¶".

You will either need to walk the related records using a looping script. Or another method is to make a calc field in the babies table, Baby_info, = BabyFirstName & " " & BabyLastName & " " & FullAge).

Then you could do:

& "Child(ren) info: " & Substitute(List( Babies::baby_info) ;¶; " ") & ".¶"

[LaRetta]

I would create a pdf of the child information (via grouped report by parent) and attach it to the email - no calculation required ... email to the parent, one attachment for all of their children all handled from the Child table. It's just a different approach which would seem easier as well and the parent can keep the pdf which would be very professional. :wink2:

Edited July 24, 2010 by Guest
Added sentence

[LaRetta]

I created a small sample file for you, saralee.

On the household layout, change the email to yours. This was put together quite quickly. You can use your temp path to hold the pdf if you don't want to store it someplace (and you probably don't need to). Realizing you also wanted to include Studies, I switched the perspective to the Studies table.

But since you had so much information from household, babies AND studies then it only seems logical to produce a report instead of a convoluted, extremely long, complex and resource-using calculation.

Households.zip

[bruceR]

Agreed that the PDF has many advantages, including the possibility of much nicer appearance.

I have added a few variations, showing methods for gathering related data by script and calculation and generating a straight text email.

Households.fp7.zip

[LaRetta]

Nice. Two extra calculations, two extra table occurrences and oh, soooo simple to replicate and change if (and as) she needs it. Well that's why we all contribute on these forums. :smirk:

[bruceR]

Thanks. Example updated to use sorted relationships, thus putting data in (probably) a more preferable order.

Households.fp7.zip

[comment]

two extra table occurrences

I don't see why these are required. If you happen to start from a baby, why not GTRR to the parent Household first?

[LaRetta]

I see the benefit of understanding this technique for various needs (and I've used similar to replace left outer joins when no regular paths were available) but we are here to provide a solution for saralee. Just because something CAN be done, doesn't necessarily mean it should.

So I guess I would say that, if it were acceptable to the business rules, I'd personally skip this extra work, extra table occurrences, extra global and calculations and simply send a pdf. But that's just me - I have a simple mind and every TO and field added increases complexity of a solution and I avoid it whenever possible. :wink2:

[bruceR]

Are you sure you're not my mother? Always the that's nice Bruce but.

[saralee]

THANK YOU SO MUCH! you guys are awesome!

Much appreciate it :