There will be a new feature coming that allows as many foreign key relationships to setup as you would like....we have been using this for quite some time (through the framework) and will release it to the general public within the next several updates.  This has actaully been discussed on a couple of other threads lately...so I recommend doing a search if you want more details...but in short there will be a mechanism for this shortly anyway.

Hi Larry. Just lurking through old messages and notice that your requirement is something I want to do in strataframe. i.e. Mutile Parents for the one Child  (i did exactly as you were doing in VFP..the IIF in a grid) Did you get a resolution ?

Regards,

Gerard

Hi Guys,

We always prefix our table and column names with a unique three character prefix, e.g. PUNWeight, HPCWeight etc. Not only does it eliminate duplicates names in procs and views it makes code easier to 'read'. Being doing this for years in various database and highly recommend it.

FYI, another convention we use and find useful is XXX_PK, e.g. HPC_PUNID, for all foreign key relationships, where XXX is the standard column prefix for the table and PK is the Primary Key column name in the foreign key table. As you can see we always name our Primary Key values as the table prefix followed by "ID".

Cheers, Peter

It's kindof funny how when a certain topic comes up it comes up everywhere.  It seems like I have had this converstaion a lot lately

Yeah, prefixes are a really good standard because it can uniquely separate one table from another.  This is a standard that I learned when I first started in the business and it turned out to be a good one.  This is especailly helpful when you start producing more complex queries such as JOINS or pulling values froma SPROC or VIEW.  Say for example that you have a field named CreatedDate in two different tables, you then have to *** an AS clause for every field in the query that is going to return a CreatedDate otherwise you will have an ambiguous field name.  SQL Server is much more forgiving when it comes to naming fields than VFP, but having prefixes has really reduced the complexity of some of my queries over time.

Peter and Trent,

Thanks very much for your input and suggestions.  I'm making good headway learning SF because of this good support. 

As a small aside, Trent, I notice you include a table prefix in all our field names (e.g.,  Pharmacy.ph_Name).  Just curious how that helps.  Is it to avoid duplicate field names in JOINS?  Since I am essentially rebuilding some VFP applications almost from scratch, I'm open to anything that prevents problems down the road.

Larry

I would tend to agree with Peter on the lookups.  We have hundreds of places that a foreign key value needs to be selected or set.  For example, we may have a patient and their preferred pharmacy may need to be selected.  So we may populate a combo box with only the values that need to be used and bind it to the patients foreign key field.  It is always good to create a custom Fill method that only brings back the required fields in this case...no reason to add overhead:

I would then use the FillForComboDisplay as the method to populate the combo...and the ph_pk would be the Value Member which is bound to the foreign key field.

BTW, Peter, thanks for your nice words regarding our support! 

As you may have noticed, we have a wrapper for both DevExpress and Infragistics.  Ultimately we use some of the Infragistics (after first using DevExpress) controls in our medical software.  But we have create custom controls and continue to extend our StrataFrame controls due to one isolated issue or another.  DevExpress and Infragistics are both good control companies, but we predominately use the SF controls except in areas like the UltraGrid or controls of that nature.  Infragistics also has some good masking controls (i.e. currency) but they still take some tweaking to get it just right.

Larry,

Whether the column you are trying to display is read-only or not I still believe a lookup is the answer, the only difference being that for the read-only case you need to ensure that the lookup, however it is implemented, is set so that it disallows editing/changing.

I tried using BOs based on views when I was getting started with Strataframe, and found that it just became too complex if I needed it to be updateable. That is not to say it can't be done, and in some places is preferable, but I wouldn't use one to try and maintain a table.

My experience with Devexpress is very positive, but I have no experience with alternatives. Their support is good (though not as good as Strataframe). There are a reasonable numbers of devexpress users on this forum, and has been considerable discussion in other threads, so I suggest you do a search.

Hope this helps.

Peter

Peter,

Thanks for the additional info.  You summarized the issue nicely.  A relational database, by definition, has lots of relations (doh!).  This means tables have lots of foreign keys that are themselves meaningless to the user, who is interested in some value looked up on the related table.  How to best display these looked up values on forms, grids, lists, etc... is what I'm asking about. 

A combobox does this automatically, but I'm talking more about readonly situations.  Back in VFP, I used to put a little IIF(Seek(),lookupvalue,"") in the readonly textbox.refresh() to do the same thing.  Way back in dBaseII (dating myself) I used to use the multiple relations.  But in grids and so forth, I used to use multi-table views.  Your DevExpress Loookup control sounds nice; interestingly, I have a little system for Palm applications called Satellite Forms that offers a similar control. 

I don't mind spending money on good tools.  What do you think of DevExpress?

Larry

Larry,

I'm using DevExpress a well as strataframe with a SQL Server database back end. With Devexpress the UI elements I use for this purpose are RepositoryItemLookupEdit on a Grid and LookupEdit on a form. Using Strataframe without Devexpress I guess that a Strataframe Listbox or ComboEditBox probably does the same thing.

The fundamental issue is that the UI element is bound to the field (that contains a foreign key) of the BO that you are trying to display, and has been populated with alternatives based on your lookup BO which sets the value of the foreign key, but displays something more meaningful.

Peter

Peter,

Yes, this is what I am trying to do.  What is a "lookup"?  (I'm coming from VFP and am new to both SQL and .NET).

Larry