Jump to content

Relationship to empty field


mcyrulik
 Share

This topic is 5671 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5671 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.