Relationship to empty field

[mcyrulik]

If I have Table1 that can have multiple items in table2, is there a way to set up a relationship that will show any items in table2 that aren't associated to a parent record in table1?

So basically show a portal that contains all the records where the key field to table1 in table2 is empty.

[John Mark Osborne]

You can show all related records in a portal, regardless of whether the key fields match, using the cartesian product operator (x). However, it sounds like you want to show all of the records in the child table if there aren't any specifically related to the current parent record. But, if you have one or more child records with the same key as the current parent, you want to show just the related records. Is this correct? Also, why do you want to do this so I can make sure there isn't a better alternative approach?

[mcyrulik]

Yeah, I already know about the (x), but that will give me all the records from that table, whether they have a parent or not. I really only want all the ones that don't(key field is empty). I want to show "child" records that aren't related to ANY parent record. So if my relationship went

table1::ParentID--Table2::parentID

I would like my portal to show any records from table2 where parentID is empty.

Basically the scenario is this(not my decision, I just get to program it)

Jobs run through our shop. When one is complete, if there is something that physically needs to ship(most often it is emailed or ftp'd), the Account Rep will create the packing slip item portion of a packing slip.

When the receptionist(who handles routing of drivers) is getting ready to send a driver, she will create packing slips for deliveries. To these packing slips, she will add the items that the Account reps have already created.

This is where the empty field comes in. I need the portal to show only those items that have not been added to a packing slip yet.

The reason the account rep is making the packing slip item is because they need to enter info that is not available or accessable to the receptionist, and because the receptionist is only responsible for gettingvthe package out the door when it is ready, not any of the contents or packaging.

maybe not the best scenario, but it's the one I get...

[comment]

I don't think it's possible to relate to - and only to - an empty field.

However, you can define a calculation field =

Case ( IsEmpty ( ParentID ) ; "Whatever")

and a global field, or an unstored calc field, on the Parent side to hold the "Whatever" value (if your ID fields are numbers, you can use 0 or a negative number as the match value).

[Lee Smith]

I'm on my way out the door, so I don't have time to read all of the post, or to analyze this thread, but my first thought was to use the ≠ (not equal to) for the relationship?

Lee

[Razumovsky]

Another thought is a global calc (or unstored calc) In the Parent that returns a value list of all Parent ID values for Child records. Then use this in a ≠ relationship to Parent ID in the Child Table.

However, I think Comments suggestion offers advantages if the number of records to discount becomes quite large. There may be some refreshing issues with the fact that the match field in the child has to be a stored calc though.

-Raz

[comment]

I'd suspect quite the opposite.

[John Mark Osborne]

My concern is breaking relational design rules and creating a record without a value in a key field (orphan). The foreign key should always be filled with one matching parent key value. There are other ways of making a related record not show in a portal using a second matching set of keys. This could be a global field on the parent side containing the word "Complete" and a category field on the child side. If the child shouldn't display in the portal, set the category to "Incomplete" using a popup menu. As soon as everyone is done with the child record and it should display, they can set the category field to "Complete".

[Razumovsky]

Exactly the opposite?

Value list would be better for large number of discounted records, but more prone to refreshing issues?

Could you elaborate your suspicions please?

-Raz

[comment]

Sorry, that was a bit too terse. I am talking about refreshing, of course.

I see no refreshing issue with a stored calc per se, and certainly not in this case. Say you place two portals on a parent layout, one showing the parent's children, the other showing the orphans (using the Case ( IsEmpty ( ParentID ; ... flag as a match). If you change the status of a child in any of the two portals (by filling/deleting the ParentID), the record will fly over to the opposite portal without so much as a hiccup.

OTOH, relationships based on keys derived from ValueListItems() almost always have refreshing issue. It would be hard to demonstrate this with a similar example here, though, because here the list is based on the ParentID. I find it difficult to point to an element that could change, requiring a refresh, but not triggering one anyway.

[comment]

I don't quite see the need for concern here. The records must be orphans, because they represents orphans in real life. They cannot be assigned to a parent - because their parents have not yet been created. I don't think there's anything wrong, or even unusual here. You could compare this to assigning Contacts to Companies. Some contacts are indepedent, so their CompanyID is empty.

[comment]

While writing the above, it occured to me that the calc field could be eliminated by having the real field (Child::ParentID) auto-enter a default value of "0", for example.

[Razumovsky]

If you change the status of a child in any of the two portals (by filling/deleting the ParentID), the record will fly over to the opposite portal without so much as a hiccup.

Yes, I was remebering a demo (of yours, I think) that showed the same technique when I read your first post. I think I am still nervous (irrationally?) about using calcs on both sides of a relationship without fully understanding the work flow. I susspose I should be just as nervous about value lists, but since this one seemed static...well so does the calc now...

The auto enter is definitely a better option.

[comment]

You should have no fear of stored calcs. A relationship based on a stored calc should refresh in the same way as a relationship based directly on the fields referenced in the calc. The only advantage of the auto-enter is in eliminating a field.

[mcyrulik]

Thanks for all the input, everybody!

I ended up using the auto enter of -1 for the for the child ID, relating to a global in the parent table that has -1 in it.

Works great.