Conditional relationship in 7

[LindaG]

Apologies if my questions seem confusing - seems I only post questions when I'm confused, otherwise I wouldn't need to post.

I have file with two fields: CompanyName and IndividualName. Only ONE should ever be filled in (and this fills in the CustomerName with auto-enter replace). I've tried validation and it works, but staff puts the persons name in Company because they think, because it's a Customer, that's where it goes. I have decided to try to help them by forcing them first to select whether it is a company or an individual and only presenting that field for them. Otherwise, they try to also put an employee in the Individual field, which is wrong (I have related file for company contacts).

I have a radio with Company and Individual (IndComp). When one is selected, I want the appropriate field to appear next to that radio selection. But ONLY that field - not the other, as:

Is this new Customer a:

o Individual ............ [ Individual field ]

o Company ............... [ Company field ]

Clever as I thought I was, I decided I could put each field in a portal.

1) I created a self TO called CustomerType. I joined UniqueID = UniqueID and IndComp = IndComp.

2) I created a portal and placed the CompanyName field in it. I created a second portal (same CustomerType TO) and placed the IndividualName in it. Seems fine so far.

However, when they click the radio, both fields appear. Wrongo!! I obviously need a different portal to keep them both from showing but I'm still missing something here! How can I force only one or the other to appear? I think I need a global instead of IndComp in these joins, right? But then how do I populate the real IndComp field if they fill in a global? And do I need two globals?

The reason I'm doing this is Staff constantly enters the info wrong. If I can only present the proper field, they won't be confused. However, I've totally confused myself on how to unconfuse them.

Linda

[LindaG]

I'm convinced I need a second TO to separate the two portals. But I get a circular join error when I try to add a calc to CustomerType TO of = MainTable:IndComp. I can't match the right-side to the left because the right-side IS the left.

I must need to do something on the left side, but I don't know what.

[ESpringer]

Your relationship currently just matches ID to ID (good); and then indcomp to indcomp, but every record has the same value there as itself. So this criterion can't offer any further constraint on the ID self-join.

What you need is two different two-criterion relations, both starting with ID = ID: then one adds match between gIndividual (a global field that contains the word "Individual") and indcomp; and another adds a match between gComp (a global that contains the word "Company") and indcomp. Then a separate portal on each relation should do the trick.

(Or you could use more streamlined values such as 1s and 0s, with text labels on the layout for clarity.)

[LindaG]

Oh! I have never had something explained more clearly, ESpringer! I can't thank you enough! I kept going to that thinking but kept losing myself - 2 globals on the left (one for Ind and one for Comp) Two portals!

I think what kept throwing me is that, in 7, that little box that allows relating in various ways now, appears to provide all I should need. But it doesn't provide that additional piece to filter further, as you say. And, in this respect, it's like vs. 6.

So the portals will need to be (both) additional TOs but their only purpose is display of the real fields. I'm not sure how to then set the real IndComp field with the selection if I'm clicking a global to display the fields. Other than that, I think it will work well. I've never tried attaching script to radio before to set a field with what is clicked. Too bad I couldn't use auto-enter replace on that IndComp field by calculation = gIndComp. That would be nifty. Any ideas about setting the real field when they click that global radio button?

Oh thank you for helping me. You are a life-saver!!!

Linda

[ESpringer]

what kept throwing me is that, in 7, that little box that allows relating in various ways now, appears to provide all I should need.

There is actually *one* way in which things can be more efficient in 7, which I neglected to mention: If you are sure that users can only enter one of two values (1 or 0, or "Ind" or "Comp", whatever) into the IndComp field, then you could have just *one* global, and have one variant on the self-join relation match gInd to IndComp (This relation-variant will pick up the Individuals), and the other variant use the not-equal sign (This relation-variant will pick up the companies).

So the portals will need to be (both) additional TOs but their only purpose is display of the real fields.

yes, they'll each need their own TO of your main table, with different path criteria.

I'm not sure how to then set the real IndComp field with the selection if I'm clicking a global to display the fields.

You shouldn't need to click a global at all... the global field doesn't even need to remain on the layout after you get the right value into it the first time. The global is just a way of getting your self-join relation to pick out, say: {all records that share this ID and have "Ind" in the IndComp field}. The global serves as a place to hold the "Ind" constraint, since you can't actually specify any value directly in your Define Relationships dialogues.

You can continue to use radio buttons to get the user to choose "Individual" or "Company" for your Customer Type (which I might store as 1/0 behind the scenes, adjusting global to correspond, if your database needs streamlining). If you still believe you need scripts, say why... I bet you don't.

[LindaG]

Okay, I want to be sure I understand this theory, as this is so important. I will change Individual to 0 and Company to 1. Create one global text. Put a 1 in it then delete it.

Individual portal would be:

ID = ID

AND

global <> IndComp

Company portal would be:

ID = ID

AND

global = IndComp

Am I getting the theory here? Because, when someone clicks 'Company', it will make the 'right side' a 1 which will complete the relationship to the global for that TO! Is that right?

I'm wondering though ... before someone clicks IndComp, using global <> IndComp will display the Individual field, won't it? I don't want either displayed until they select from IndComp. But I'm getting excited about this, because if I can burn this concept into my mind, 7 will become much easier for me.

[ESpringer]

Create one global text. Put a 1 in it then delete it.

make the field a number field if you're going to go with 1 and 0. Don't delete the global FIELD after entering the 1 -- You can take the field off your layout, though.

Individual portal would be:

ID = ID

AND

global <> IndComp

Company portal would be:

ID = ID

AND

global = IndComp

Yes.

If you're going to do it with 1 and 0, you'll want to "mask" the real values of the radio buttons with text labels ("Individual", "Company").

I'm wondering though ... before someone clicks IndComp, using global <> IndComp will display the Individual field, won't it? I don't want either displayed until they select from IndComp.

Ah, I assumed you might have an auto-enter value (a default "Company" or "Individual" set for new records, perhaps wise if one predominates). But if you want the radio buttons to start out blank, and want neither data entry portal field to be available to start out, then you ought to go with the earlier 2-global-left-key solution (rather than the match and non-match variants).

Note: one thing that may not be optimal about this solution is that you *can* get data in both fields: if someone *changes* the radio button after entering a company name (realizing "oops, this is an individual"), the company name field is still filled (though not visible), and now the user can enter individual name as well, once that becomes visible. I'm not sure what your database is doing with the different name fields... (I'm not sure why you even need two different name fields, rather than one name field, which is the name of *either* the individual or the company, as specified by the IndComp radio button...) but assuming you need both, then there might be various ways of preventing stray data... Keep us posted.