Calculating a 'list' out of portal rows???

[michele]

Here we go:

I need to make a list of the data in my portal.

for example:

portal row 1: "a"

portal row 1: "b"

portal row 1: "c"

needs to return:

a

b

c

in my calculation field

How do I calculate this?

Thankyou so much for helping me out!!!

------------

Thankyou for reading this message but I found my answer all by myself (like a big girl)

The 'copy all script step' returns the values with a carriage so I can paste them in one text field...as simple a that, hopefully my question/answer will be usefull to some of you.

[ August 24, 2001: Message edited by: michele ]

[Ocean West]

You want the results to be calculated not updated via script?

If you want this function with out a script you can use a plugin...

The Troi-Text Plug-in adds interesting text functions

[Ocean West]

Or

Create a calculation with the design function:

ValueListItems("DatabaseName", "ValueList")

The value list should be result based on the relationship.

[BobWeaver]

Wow! That's really clever!

[Rigsby]

Wow, Wow, Wow! That really is clever, and just solved about another 10 probs for me :-) Cheers,

Rigsby

[Stuart T]

WOW WOW WOW WOW WOW Can't add much more than that.

[michaelzap]

This is a beautiful little trick, but...

I got it to work perfectly in one situation, but in another it's completely freaking out. I don't understand why. Maybe someone can help.

I have a foundation database of donors and donations which also includes people is whose honor a donation was given in the same "donors" database. The donations database is basically a typical invoice setup with a portal for line items. Each line item in the invoice could be a donation in honor of a different person, although all of them are being made by the same donor. So what I'm trying to do is to create a match field in my donations database of all the honoree IDs from within the line items. It's not only not working, it's doing the weirdest things.

Lets say I add a line item to an invoice (donation) made by donor #1 and list in that line item that it's being give in honor of donor #7 and another that is being given in honor of donor #3 and yet another given to no one in particular. My resulting match field (from the related value list) should be 7 and 3. But I'm getting either empty fields or totally unrelated numbers (2,6,9).

I have a value list of related honorees (related by the ID number between the donations database and the line items database). I also have a calculation field which should have the ValueListItems of that value list. I don't understand why the calculation field is always either empty or contains numbers that are not the related ID numbers, this despite the fact that the value list seems to be correct! If I format a field as check boxes with that value list the boxes you can check are in fact the same as the honoree ID numbers in that invoice, but the calculation field (which should just be a list of those very same boxes, right?) is something different.

I've checked and double-checked my relationships and field types (changing them all to text or number, etc.), but I haven't found the problem.

What on earth could be causing this?

[Charlie]

Well, you've basically lost me on that description.

As I understand it, you have one db with your donors, and a second db with their contributions (line items). One field in <<line items>> allows you to enter information that may or may not tie back into <<donors>> (the "in the honor of" field).

Is the "in the honor of" field a pop-up list or similar drawn from the <<donors>> db?

Now you want to create a calculation that shows you the values of all line item "in the honor of" donors for the actual donor in question? What is the purpose of this list?

What do you mean by "create a match field"?

I've now confused myself. More info on what you're trying to accomplish would help, I think.

Keep trying.

C

[michaelzap]

You're right - my description is rather confusing...

Lemme try again:

I have a number of related databases. One of them is called "Donors", but it should probably be changed to "Contacts" because it also includes contact info for people who've never donated anything but who have received donations in their honor ("honorees"). Anyway, for now it's called "Donors"

Another is called "Gifts", and it has information on all donations made (as well as merchandise sold, etc.) - essentially an invoice database. Each record in "Gifts" has only one related Donor ID (the "buyer"). Gifts also has a portal for invoice line items drawn from another database called "Gift Itemization".

"Gift Itemization" is another related database (related to Gifts by Gift ID, related to Donors by Donor ID and also by Honoree ID). Each line item in "Gift Itemization" may have an Honoree ID (thus far I'm only entering them manually, but eventually they'll be entered using a script) in whose honor the gift is being made. This ID is the same as Donor ID in the "Donors" database, but the relationship is a separate one for Honorees. This because a person may make several gifts in one invoice, and they may make them in honor of different people.

I'm creating the line items in the portal within the "Gifts" database. What I'm trying to do that's not working properly is to generate a list in each "Gifts" record (each invoice) of all Honoree IDs in that record's line items. I want to use this list as a "match" field, meaning that I want to be able to list gifts given in name of a person in the "Donors" database in a portal within that database by "matching" the Donor ID in the "Donors" database with this list in the "Gifts" database, as well as perform summaries and calculations with this info. I have a similar portal "matching" gifts given by each person in the "Donors" database, but it's somewhat different in that each record in "Gifts" has only one related donor, whereas it may have several related honorees.

The problems seems to be not in the "matching" stage, but before I even get that far.

I defined a value list ("Related Honoree List") as the related (by Gift ID) values in the Related Honoree ID field in "Gift Itemization". Then I made a calculation field of those ValueListItems, which is what I'm trying to use as my "match" field. The value lists seem to be correct in that when I format a field using them they are in fact checkboxes for each Related Honoree ID that I've entered in the Gift's line items, but the calculation field is either blank or filled with other values that are not the same as the value list's options.

Strangely, when I format the calculation field as this value list, instead of all the checkboxes being checked (since the calc field should be all the ValueListItems, right?), I get what seem to be random results (sometimes some of the values coincide, in which case those boxes are checked, sometimes nothing is checked).

Does this situation ring any bells for y'all out there?

THANKS for trying to muddle through this one with me.

[smorr]

Originally posted by michaelzap:

[QB]You're right - my description is rather confusing...

Lemme try again:

I have a number of related databases. One of them is called "Donors", but it should probably be changed to "Contacts" because it also includes contact info for people who've never donated anything but who have received donations in their honor ("honorees"). Anyway, for now it's called "Donors"

--snip --

Ok -- that is still pretty abstract. could you provide some data examples .. eg.

Donor "Bob smith" has these items , yada yada?

Scott

[ September 16, 2001: Message edited by: Scott Morrison ]

[smorr]

The valuelistitems function is even more useful to create local keys for accessing data and compounding those keys -- Creates great on the fly portal filters sans plugins.

examples:

I have an student markbook solution where I have

studentDB

classesDB

Enrolment DB - a link file with both Student ID and Classes ID.

AssignmentsDB Containing assignment information for a class but not directly for a student.

I can get assignments for a particular student this way:

1. In studentsDB create a value list "classIDList" based on students::studentID = enrolment::studentID.

2. Create a field: [values]classIDList as a calculated field = valuelistitems(status(currentfilename),"ClassIDList) [field type text]

3. Create a relationship to assignments where students::[values]ClassIDList == classid

4. Create a portal based on above relationship and show me the assignments for the student.

------------

I can further this to get only the assignments in the next 7 days

1 create a Key in assignmentsdb

[key] classID|DueDate = classid &"|"& duedate -- a text field

2 in Students database create another key based on valuelist and using the substitution text function to attach a day to the end of each classID item

[key] upcoming assignments =

substitute([values]classIDList&"

[BobWeaver]

Michaelzap:

I didn't completely follow your explanation how you have things set up but obviously you must have the value list set up as a "use related values" type. That being the case, the value list is going to change dynamically as you change the related records. However, if you are using the value list items in a calculated field, it may not know that the value list has changed and will not recalculate at the right time, thus giving wrong results. The fact that your actual value list is correct but the calculated field is not, indicates that it is just not calculating at the right time. I set up something like this and force the calculated field to recalculate after every change to the line items by setting up the calculated field like this:

ValueListItems("MyDB","MyValueList")&Left(Sum(LineItemFile::DonationAmount),0)

The last part of the formula simply forces the field to recalculate whenever a change is made to a line item.

Also be aware of another limitation of this method: Duplicates of items will not appear (usually this is a good thing).

[michaelzap]

Excellent! You saw right through my obtuse explanation and hit the nail right on the head. Thanks!

I do have one minor issue still, however. Using your

&Left(Sum(LineItemFile: onationAmount),0)

trick does indeed make the field recalculate properly, but it also makes it un-indexable (because it's doing an unstored calc on the related field). Any suggestions as to how I might both force it to recalculate AND be able to index it (so that I can make a relation between it and the IDs in the Donors database)?

Or is that asking for a combination of two opposites?

[BobWeaver]

Hmm, maybe instead of:

&Left(Sum(LineItemFile::DonationAmount),0)

Try this:

&Left(RecordModTime,0)

where RecordModTime is a time field defined to auto-enter the modification time, though I'm not sure if it will work when you edit portal records.

You could switch from using related fields to looked up fields. There's a million variations on this. You may have to experiment until you find one that works for your situation.

[jgw]

Would someone please give a concise example of this value list trick? I am having trouble understanding how each file must be set up and how the function actually works.

Thanks

[jgw]

Thanks for the very clear reply! Cool trick!

JW

[BobWeaver]

Okay, I'll give it a shot.

Suppose file C lists school classes (one record per class offered), and file S lists students. In file C you want to display a list of students for each class. Both files will have a classID field, and file S will also have a student name field. There will be one record in file S for each instance of a student/class. So, if Bob is enrolled in Mathamatics and History, there will be two records for him in file S:

Bob, MATH101

Bob, HIST108

Likewise, if Sue is in History, English and Chemistry she will have 3 records:

Sue, HIST108

Sue, ENGL204

Sue, CHEM200

Now, in file C:

1. Set up a relationship "S" to file S using the classID field in both files.

2. Create a value list "V" using values from a field. Select "Only use related values." Pick relationship S and the student name field.

3. Create a calculated field cClassList with this formula:

ValueListItems("S","V")

4. Place classID and cClassList fields on your layout.

5. Create a record for each of the classes:

MATH101

HIST108

ENGL204

CHEM200

6. Your layout should now show a list of students in each class.

However, if you go back into file S and create a record for Henry in ENGL204, the change won't be reflected in the cClassList field in file C because Filemaker doesn't track those changes in determining when to recalculate the formula. You can force it to instantly update by modifying the formula for cClassList as follows:

ValueListItems("S","V")&Left(Count(S::classID),0)