Display Related Values In Master File

[MarkWilson]

I am working on a photo directory. I currently have a database containing a family record (family picture, parent's names, address, phone number...). I am using a portal to enter the children's name to a related database. I want to be able to display the children's name (eldest to youngest if possible) in the master file for previewing and printing.

The sub file contains the children's names, birth dates, email... based on .

Any ideas on how to accomplish this?

[-Queue-]

You can either use a portal with the relationship sorted in ascending order based on birthdate or you can create a value list using 'Only related values' based on the related name field, 'Also display values from' the related birthdate, and 'Sort value list by' second field. Then create an unstored calculated field of ValueListItems( Status(CurrentFileName), "yourvaluelist" ). Put this field on your layout and format it as deeply as necessary to display as many rows of names as you anticipate. Set it to slide up, so that it doesn't take extra space when previewing/printing and there are fewer names.

[MarkWilson]

Thanks -Queue-

You could not describe anymore accurately what I tried. I got it working by removing 'Also display values from' the related birthdate, and 'Sort value list by' second field. (Leaving the 'Also display...' and sorting by the first works too.)

I don't understand why it stops working when the sort is by the second field, but it works now.

Thank you for the input.

[-Queue-]

Hmm, sorting by the first will put it in alphabetical order. This would only work if the ages of the children in each family followed the alphabet in reverse order. What was the result when you included the birthdate field anyway?

[MarkWilson]

Sorry for the confusion. I mean able to display.

If I add the second filed, "d_ChildBirth", to the relationship and sort by that field, the calculatin fails. If I sort by the "ChildName" field or only have the "ChildName" field in the relationship the calculation performs correctly (sorted by "ChildName").

The second related problem is that I developed in 5.5 and the solution is being used on a 5.0 machine. Both systems run Windows XP. With the latter scenario, everything calculates correctly on 5.5. The 5.0 machine shows one family's children over all records. I have checked to ensure that it is not an ID problem. The children all show up in the portal with their respective families and I reviewed the ID field itself.

Is this an upgrade area from 5.0 to 5.5?

Thanks

[-Queue-]

Can you explain what you mean by 'the calculation fails'? What is the exact result you get in the field?

I'm not sure about the 5.0 problem. Which version of 5 are you using, v3?

If you zip and attach a sample of your file, I can investigate why the calculation doesn't seem to work.

[MarkWilson]

-Queue-

By fails I mean, nothing is returned.

I will check on Tuesday which version of 5.0 is being used. Today is a holiday in Canada.

Attached is a .zip file.

Thanks for checking this out.

PhotoDirectory.zip

[-Queue-]

A simple problem. There is no date in any of the related children's Birthday field. When you specify a value list to sort by a second field, the second field must have a value in order to determine uniqueness. If there is no value, then they can't be sorted and the value list returns null.

If you include the second field, but sort by the first, then you will see all of the names in the list even if the second field is empty.

All you need to do is enter birth dates, and you will see all the children in the list, sorted correctly. The catch is that, if there are twins, triplets, etc., only one of them will be displayed, since the date determines uniqueness. However, you can use a calculated text field of Year(d_ChildBirth) & "_" & Month(d_ChildBirth) & "_" & Day(d_ChildBirth) & "_" & ChildName in the related file, and sort based on that field instead. Since the combination of birthday and name will not be the same, all the children's names will show, and since Year is first, followed by Month and Day, they will sort correctly also. In addition, sorting by the calculated field will make children without a birth date entered show, but they will be sorted to the bottom of the list since the first character in the resulting calculation would be an underscore "_".

[MarkWilson]

Thanks -Queue-

All is good in 5.5 land now.

I went with the calculated version.

[MarkWilson]

I just installed the updated version on my clients 5.0v3 machine.

The problem is that the children's names for the current record are displayed for all records in the Preview mode or when it is printed. The children's names return correctly in Browse mode when forced to recalculate by changing to records off the screen. On my 5.5v1 version all is as expected.

The unstored calculation for children's names is:

Substitute( ValueListItems( Status(CurrentFileName) , "Family Children Names" ) , "

[-Queue-]

I don't have 5.0 on this machine and have no idea why it should behave differently.

Nice work on the z_Sort field. Since you're not converting the date to text, it returns a number that sorts correctly (the number of days since 1/1/0001).

[Lee Smith]

Can you post a sample of your files, I have both 5.0.3 and 6.x on my machine. I could then compare the files as they appear in both if you want.

HTH

Lee

[-Queue-]

Hi Lee, there's an attachment about 5 posts up from yours. It only requires the addition of the z_Sort_dBirth_ChildName field and changing the value list to sort by it.

[Lee Smith]

the file is missing other things, it is asking for the field "filed_ChildBirth" and who knows what else. I think that Mark should post a current file.

Lee

[-Queue-]

There is no field_ChildBirth. It's d_ChildBirth. Just add the z_Sort field and change the value list to sort by it. It's easy.

[-Queue-]

Like this.

ver2.zip

[Lee Smith]

Sorry about that, I pasted your calculation "d_ChildBirth & "_" & Trim( Substitute( ChildName , "

[MarkWilson]

Thanks to you both for continuing on without me,

Lee, the specific trouble in 5.0v3 is the display of the children's names on the Print "A" layout in preview mode and when printed. For my client they are displaying and printing the names of the first record's children on all the records. Can you recreate this?

However, all is good in 5.5 land.

Thanks for the help.

[Lee Smith]

The Good News (I think):

I couldn't recreate your problem using Mac OS 9.2, FM 5.0.3.

Everything displayed and printed as you want.

The Bad News (I think):

When opening your files, I got an ATM warning that it needed to open a font called Perpetua. When you look at the files, the only font I see at the Layout level is Verdana?? I looked at your file using a Utility called FMChecker which showed that you have 124 Fonts used in this file. I sure don't see them, but to me this sounds like a file on the edge of failing.

HTH

Lee

[MarkWilson]

Thanks for the check Lee,

Your font assumption is correct, the only font should be is Verdana.

I ran the free trial version of FMChecker and confirmed your results. Thanks for the heads up

[Lee Smith]

Hi Mark,

Very strange behavior.

By chance are these files modified from a different project?

Here is what I tried just for the heck of it.

I selected all items on the layouts and unlocked locked items. I then selected the Verdana Font from the Text Menu, and it replaced all items with Verdana. However, FMChecker still shows 124 Fonts used. The good news is, that ATM stopped telling me about the Font.

I even tried Recovering the File, not change (i.e. 124 fonts).

If you find a solution to this short of recreating the files, please pass it on to me.

Lee