Multiple Linking Columns in Grid?

Hey All,

Just wondering if there is an easy way to link a grid on a form by
multiple ids in VFP 6? Whenever I Google on this I get too much
irrelevant results (I'm obviously not searching on the right thing)

As background right now we have companies and contacts at the company...
Contacts obviously tie to the company by an id. We also track
interactions with a contact or company. The way it was originally
designed you could enter, for example, a billing notice on the company
level and then hit a button to clean that down to all the contacts at
the company (it basically checks if interaction X is already present at
the contact level and then inserts it if it isn't)

What we'd like to do is instead create a single table and then display
in the grid on the contact form all interactions where EITHER the
contact id matches the contact id in the interaction OR the company id
matches the company id in the interaction.

We have considered just linking on the company id but for a variety of
business (non-technical) reasons that just won't work with our work flow.

Is there a relatively easy way to do this?

Thanks in advance...

Matt

Mark
Wed May 07 02:21:22 CDT 2008

Matt

Yes it is perfectly possible to do this. You would need to do a select SQL
command to link your tables together and specify your links as outer joins,
by doing this correctly you would get information from one table that did no
match the information with the other table. The other thing you would need
to handle is that for every field returned by the select statement from the
table that did not match, you would get a null value. So you would either
need to use nvl to make sure you get a correct return or handle this in the
grid with a dynamic control or something. This would then build a cursor
that can be displayed in your grid. hope this points you in the right
direction.


"Matt Neimeyer" <matt@neimeyer.org> wrote in message
news:uOm03r4rIHA.3604@TK2MSFTNGP03.phx.gbl...
> Hey All,
>
> Just wondering if there is an easy way to link a grid on a form by
> multiple ids in VFP 6? Whenever I Google on this I get too much
> irrelevant results (I'm obviously not searching on the right thing)
>
> As background right now we have companies and contacts at the company...
> Contacts obviously tie to the company by an id. We also track interactions
> with a contact or company. The way it was originally designed you could
> enter, for example, a billing notice on the company level and then hit a
> button to clean that down to all the contacts at the company (it basically
> checks if interaction X is already present at the contact level and then
> inserts it if it isn't)
>
> What we'd like to do is instead create a single table and then display in
> the grid on the contact form all interactions where EITHER the contact id
> matches the contact id in the interaction OR the company id matches the
> company id in the interaction.
>
> We have considered just linking on the company id but for a variety of
> business (non-technical) reasons that just won't work with our work flow.
>
> Is there a relatively easy way to do this?
>
> Thanks in advance...
>
> Matt

Matt
Wed May 07 08:22:03 CDT 2008

<sigh> I was hoping that it was as easy as setting a filter of some sort
in some place I wasn't seeing. But yeah I see the concept...

Thanks

Mark Burgum wrote:
> Matt
>
> Yes it is perfectly possible to do this. You would need to do a select SQL
> command to link your tables together and specify your links as outer joins,
> by doing this correctly you would get information from one table that did no
> match the information with the other table. The other thing you would need
> to handle is that for every field returned by the select statement from the
> table that did not match, you would get a null value. So you would either
> need to use nvl to make sure you get a correct return or handle this in the
> grid with a dynamic control or something. This would then build a cursor
> that can be displayed in your grid. hope this points you in the right
> direction.
>
>
> "Matt Neimeyer" <matt@neimeyer.org> wrote in message
> news:uOm03r4rIHA.3604@TK2MSFTNGP03.phx.gbl...
>> What we'd like to do is instead create a single table and then display in
>> the grid on the contact form all interactions where EITHER the contact id
>> matches the contact id in the interaction OR the company id matches the
>> company id in the interaction.

Craig
Wed May 07 09:29:03 CDT 2008

Filters are rarely a good idea for a grid as the thumb in the scrollbar
doesn't work as expected. This becomes a problem for the user.

--

Craig Berntson
Microsoft MVP

-------------
"Matt Neimeyer" <matt@neimeyer.org> wrote in message
news:eF8dXVEsIHA.2068@TK2MSFTNGP05.phx.gbl...
> <sigh> I was hoping that it was as easy as setting a filter of some sort
> in some place I wasn't seeing. But yeah I see the concept...
>
> Thanks
>
> Mark Burgum wrote:
>> Matt
>>
>> Yes it is perfectly possible to do this. You would need to do a select
>> SQL command to link your tables together and specify your links as outer
>> joins, by doing this correctly you would get information from one table
>> that did no match the information with the other table. The other thing
>> you would need to handle is that for every field returned by the select
>> statement from the table that did not match, you would get a null value.
>> So you would either need to use nvl to make sure you get a correct return
>> or handle this in the grid with a dynamic control or something. This
>> would then build a cursor that can be displayed in your grid. hope this
>> points you in the right direction.
>>
>>
>> "Matt Neimeyer" <matt@neimeyer.org> wrote in message
>> news:uOm03r4rIHA.3604@TK2MSFTNGP03.phx.gbl...
>>> What we'd like to do is instead create a single table and then display
>>> in the grid on the contact form all interactions where EITHER the
>>> contact id matches the contact id in the interaction OR the company id
>>> matches the company id in the interaction.