self join: can i omit records with an empty 'follow up date' field?

[bhamm]

i've got a self join 'notes' table, containing a client's call history. Some of these note will have a follow up date attached to them.

right now, when i (brian) log in, i see the last 5 notes left flagged with my name. I also have a global field to allow adjusting the date range ('show brian's notes scheduled over the next [5, 10, 30] days').

i'd LIKE to see only the notes that have a follow up date. Can't figure out what to add to the relationship. Thinking of creating a calc field that sets itself to "1" for notes with a follow up date and doing something with that, to filter out the non-important notes (the ones not requiring a follow up)..

other suggestions?

thanks much!

[bcooney]

I think that you are misunderstanding what a self-join is, because I see two tables mentioned in your post, "Notes" and "Clients". Any relationship btw them is not a self-join.

Can you describe your portal in more detail? What layout is it on and what TO is that layout based on? What fields are currently used in the relationship? (Are you on a Client record or a Staff record)? Do you want to see all Notes that need followup for this client or just the ones that you are assigned or be able to switch from View All to View Mine?

[bhamm]

I think that you are misunderstanding what a self-join is, because I see two tables mentioned in your post, "Notes" and "Clients". Any relationship btw them is not a self-join.

Can you describe your portal in more detail? What layout is it on and what TO is that layout based on? What fields are currently used in the relationship? (Are you on a Client record or a Staff record)? Do you want to see all Notes that need followup for this client or just the ones that you are assigned or be able to switch from View All to View Mine?

sorry for the confusion..

on a 'client' layout, there is a 'notes' portal showing all notes attached to this client. I have a second 'notes filter' (table occurance) joined to 'notes', allowing me to filter these by staff and by due date. All of this works great.

a fair number of these notes are benign though, and have no due date assigned. The ones WITH a due date are the ones i'm interested in showing in the 'notes filter' portal. I don't mind the unimportant notes, but i'd rather omit them from this portal if possible.

thanks

[Matthew F]

I'd LIKE to see only the notes that have a follow up date. Can't figure out what to add to the relationship. Thinking of creating a calc field that sets itself to "1" for notes with a follow up date and doing something with that, to filter out the non-important notes (the ones not requiring a follow up)..

I think that you're on the right track here. Make a calculation field that sets itself to '1' when the proper conditions are met, and create another global field that is set to '1'. Then add a connection between the two to your self-join relationship (in addition to whatever other critieria you need, such as clientID).

[bhamm]

I think that you're on the right track here. Make a calculation field that sets itself to '1' when the proper conditions are met, and create another global field that is set to '1'. Then add a connection between the two to your self-join relationship (in addition to whatever other critieria you need, such as clientID).

thanks.. i'll explore this more tomorrow.

i could set a calc field (ie. 'status') to "action" while a note contains a follow up date. Then, set another field (global) to "action" and match everything up.

good deal. I wanted to see if heading that direction would be considered 'sloppy' :

[bcooney]

Why not add a global date field to the Client table that filters Notes whose followup date is less than or equal to the global? To filter out Notes that have been completed, add a flag calc field to Notes that equals 1 if the date completed is not empty.

So your final relationship to the filtered Notes portal is:

__kP_ClientID=Note::_kF_ClientID

gDate >= Note::FollowupDate

Constant 1 <> Note::flag_Complete

EDIT: Please, let's not mention self-joins again!

Edited August 11, 2008 by Guest