If *any*...

[fmphysio]

I don't quite understand how to write a calulation to take into account that there may be more than one record to look at.

What I have is 2 tables, one with people and another with the tasks for all people - with entries related to a person. There may be up to 3 entries (or tasks) for any one person. I have another field marked complete, which I enter the date to say the task is completed.

I want to enter either a 1 or a 0 in a field on the persons record depending on whether any of the tasks do not have a complete date.

The calculation I'm using currently:

If (IsEmpty (workflow_records::records_complete ); 1; 0)

However, if I have say 3 records, and 1 is empty but the other two contain data, the result is still 0.

How to I ask it to return a 1, if any (rather than all or just the first one it looks at) are empty?

Cheers

[comment]

Unless you use one of the aggregate functions, a reference to a related field will always return data from the first related record only. In this case, you could do something like:

Count ( Tasks::PersonID ) = Count ( Tasks::DateCompleted )

[bruceR]

Let([

V1 = valueCount( List( Tasks::ContactID));

V2 = valueCount( List( Tasks::CompletionDate)) ] ;

case( V1 > 0; V1>V2 )

)

[fmphysio]

Both solutions work perfectly!

However, by changing this field to a calculation, I think it's stuffing up my portal.

Basically, I need this value either a 1 or a 0, to determine if it's shown in the portal.

If the field is simply a number, and I type in 0 or 1, everything works perfectly, but nothing works when it's calculated.

Any ideas why?

[comment]

The formula should go into a calculation field in the People table. There is no reason to put this field in the portal, as it shows only one result for each person (all person's tasks completed or not).

Basically, I need this value either a 1 or a 0, to determine if it's shown in the portal.

You've lost me there.

[fmphysio]

Yeah I'm losing myself. I've only been working with databases for the last 3 months, and I'm sorta learning as I go.

I've managed to get it working for how I'd like.

Say I have 3 people, P1, P2 & P3 -- stored in people table. They have unique IDs.

In the tasks table it looks something like this:

P1(ID) | info | completed | calculated field (1 or 0)

1 | info... | 2-2-9 | 1

1 | info--- | | 0

1 | info### | 2-2-9 | 1

2 | info... | | 0

2 | info--- | | 0

3 | info... | | 0

So basically as there is something that exists in the completed field, it's calculated as 1.

Now the portal has only the following data:

Person's name and DOB all coming from the person table. Yet I want these to appear based on the presence or absence of records in the tasks table that are marked 0.

I got around it by, creating the 0/1 field in the tasks, rather than the persons table, and creating another table (FLAGED) with 1 record, and a field that I just made a 0. This field is related to the 1/0 field in tasks.

Then created a layout based on (FLAGEGD) table with only 1 record, and created the portal, BUT the catch was I asked it to display records from the person's table, NOT the tasks.

Because, if I asked for related records for tasks, I got multiple records for the same person, (e.g. their name is listed multiple times), but if I just asked for related records from people, I only get it once.

Seems to work, but I don't know why when the field in (FLAGGED) is only related to people, through the relationship between people and tasks.

Any understand what I've just done?

[comment]

If I understand correctly (big IF), you actually have THREE tables, and you want to a portal from to show only people with incomplete tasks?

That is no simple matter, because you cannot have an unstored calculation field as the matchfield on the child side.

[fmphysio]

Yeah you're right, I was banging my head against the wall for a while trying this combination and that. So far it works and I don't understand why. I was in the hope of finding someone who could translate my noobish attempts to describe what I've done, and give me a good reason why it's working, that way I'll know what to do in the future.

While it works I won't play around with too much else. It's just a pitty that I've stumbled onto the answer, but I'm not experienced enough to know why.

[comment]

Well, you have done what I would have suggested (now that I finally understand what you have done).

The reason why it works is this:

The first relationship (from your "viewer" table to Tasks) makes only incomplete tasks related.

Every task is related to a person - but from the viewer table, only incomplete tasks are "visible", so a relationship from the viewer table (through Tasks) to People can "see" only people with incomplete tasks.

[fmphysio]

Ahhh that does make sense, and I think that's what I was trying to say, if not so elequently put.

However, is this the right way to go about it? Or is there a cleaner way of doing things?

Thank-you so much for your time and patience.

Edited April 9, 2009 by Guest

[comment]

is this the right way to go about it?

Probably. It's not the only way, but it's a simple one. I don't have the background to your solution, so I cannot say anything more definitive.