--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "martinthrelly"
> <martinthrelly@> wrote:
> >
>
> Hello Martin,
>
> Please do not abbreviate your application User Defined Fields to UDFs.
> Firebird has User Defined Functions which are abbreviated to UDFs, so
> you will get confused replies.
>
>
> > for example, lets say the user chooses to display only the grid
> > columns FORENAME, SURNAME and HAIRCOLOR (where HAIRCOLOR is a UDF). in
> > this case in order to select the HAIRCOLOR alone i must be doing
> > something like this:
> >
> > SELECT udfvalue FROM contacttype_udf WHERE contacttype = X and
> > udfdescription = 'HAIRCOLOR'.
> >
> > this is ok when i try to run it as a standalone query, but i am having
> > to do this for potentially a lot of UDF's and CONTACTS. any advice
> > much appreciated. thanks.
>
> The table design is mostly correct here. The only difference is that I
> would normalise out the udfdescription into a second table and store
> the udfdescriptionid instead.
>
> I presume that although your customers define these custom fields, the
> fields themselves are often used in more than one contact (otherwise
> how would you even do a grid like you have described).
>
> Inverting the table into a set of columns is really a function that
> should be performed on your application server rather than at the
> database level. SQL is not a really suitable language to do such a
> transform. While you may be able to hack around using execute
> statement inside a stored procedure, it will be much easier to do it
> right.
>
> Adam
>

hi adam (and helen)

thanks for taking the time to reply. yes, you are correct adam, the
UDF fields are based on CONTACT TYPE. typically there might be 10 or
15 contact types. so thats 10 - 15 possible UDF sets, if you follow my
drift.

i take onboard what you say about inverting the data. im thinking this
is what you mean...

1. SELECT the standard data from the tables.
2. SELECT the UDF data from the tables using a dynamic sql to return
only information on UDFs mapping to the grid columns on display (i.e.
per user preference).
3. Use C#.NET to invert the UDF data then join the tables.
4. Finally bind to the grid.

thanks for any confirmation.