The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Display truncated db field in datagrid
Posted: 10 Mar 10 4:30 PM
|
Is there a simple way to add a truncated database field to a bound datagrid?
Specifically, if I wanted to display only the first 3 letters for a contact FIRSTNAME in the Account/Contacts tab.
I would need some way to display only the LEFT(FIRSTNAME, 3) as a column.
Thanks for the assistance. |
|
|
|
Re: Display truncated db field in datagrid
Posted: 11 Mar 10 3:36 AM
|
There are a few ways to do this.
One way would be to create a calculated field which result the abbreviation you need and then include that as a field in the grid.
James |
|
|
|
Re: Display truncated db field in datagrid
Posted: 11 Mar 10 8:10 AM
|
| Thanks James. I thought of that and made an attempt to manipulate the string but it appears you can only do concatenations in the calculation. Is there a way to take only the LEFT 3 characters of a string in a calculated field? |
|
|
| |
|
Re: Display truncated db field in datagrid
Posted: 11 Mar 10 8:28 AM
|
I tried that. If I create the calculated field using that in the calculation tab, it literally displays in the datagrid column as "LEFT(Brian, 3)"...
 |
|
|
|
Re: Display truncated db field in datagrid
Posted: 11 Mar 10 8:38 AM
|
How about this:
Create a SQL View with a One to One relationship with the ID field and the calculated field (on SQL):
e.g. CREATE VIEW Acct_Ext_View as
SELECT ACCOUNTID, LEFT(FIELD, 3) AS FIELDX FROM ACCOUNT..
Then enable the View within the DB Manager, and create a Global Join if necessary (otherwise just use a Local Join on your group or Datagrid).... |
|
|
| |
|
Re: Display truncated db field in datagrid
Posted: 11 Mar 10 8:59 AM
|
Another method that works (but can't be used if you have remotes) is to:
Use DB Manager create a field with the name that you are looking for (e.g. MYTRUNCATEDFIELD).
Then using SQL Management Studio, modify the field definition and make it a calculated field. And off course, set the formula to what you need it to be.
By doing it this way, the field is built into the Schema that SLX uses for Group builder, Mail Merge, etc and it will return your calculated data.
Again, this is only a good approach if you are not considering any kind of remotes. |
|
|
|
Re: Display truncated db field in datagrid
Posted: 11 Mar 10 9:00 AM
|
P.S. - I used the Contact Firstname as a simple example. I was really after the Attachment File Extension. Creating the view like this gives you a very nice way to display the File Type as a groupable/sortable column in any Attachments grid.
Create VIEW ATTACHMENT_EXT_VIEW as
SELECT ATTACHID, UPPER(RIGHT(FILENAME, 3)) as FILEEXTENSION FROM ATTACHMENT
Could be useful for someone out there. |
|
|
|
Re: Display truncated db field in datagrid
Posted: 11 Mar 10 9:07 AM
|
What I have done in the past is to use the DOCUMENTTYPE column for this purpose.
Again, since I had no remotes I relied on SQL Triggers to populate it, but basically this column contained the Extension for each Attachment. |
|
|
|
Re: Display truncated db field in datagrid
Posted: 11 Mar 10 9:17 AM
|
| I saw that DOCUMENTTYPE and wondered why it wasn't populated... guess I could populate it when an attachment is inserted but having the view is simpler and foolproof. Thanks again. |
|
|
|
Re: Display truncated db field in datagrid
Posted: 22 Mar 10 2:45 PM
|
This can also be done by adding some code to the On_Show event of the form to update the grid's SQL text.
Start with opening the SQL property of the grid, and copying the existing SQL. Then replace the simple SQL below with your query, and insert this into the On_Show event, or call it as a sub proc.
Sub Set_Grid_SQL
Dim strSQL
strSQL = "SELECT A1.TableID, Left(A1.MyField, 3) A1_MyField "
strSQL = SQLTxt & "FROM MyTable A1 "
grdMyGrid.SQL.Text = strSQL
grdMyGrid.Refresh
End Sub |
|
|
|