Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
Copper Contributor
Posts: 31
Registered: ‎04-08-2010

Date field in sData editable grid

Does anyone know if or how you can format a date column in the new sData editable grid?  This is the grid that was used in the 7.5.4 opportunity products or sales order products forms.  I've added a date column (as an EditableField column), but I see no way to format it.  I've attached what the form control and grid look like.  Maybe it can be formatted in the sData call?

Highlighted
Employee
Posts: 85
Registered: ‎04-01-2009

Re: Date field in sData editable grid

You are correct that the 7.5.4 Editable Grid has neither a Date column type nor a formatting property for the Bound/Editable column type. I know it doesn't help you with 7.5.4, but maybe it will give you hope to know that there will be additional formatting capabilities for Editable Grid columns in 8.0.

 

One way you could do it in 7.5.4 would be to create a calculated field/custom property to contain the formatted date string and use a business rule to do the formatting. However, I'm told that would have an undesirable effect on performance.

Highlighted
Copper Contributor
Posts: 31
Registered: ‎04-08-2010

Re: Date field in sData editable grid

OK, in the meantime, would it be possible to add a "view" entity that changes the date fields to a formatted string, and then add that to the editable grid?  For example:

 

Create a sql view that contains this column:  select convert(VarChar(10), startdate, 101) as startdate

 

I don't want them to change the dates.  I just want to display them.

 

John

Highlighted
Copper Contributor
Posts: 31
Registered: ‎04-08-2010

Re: Date field in sData editable grid

I was able to get this to work, by creating a view with a select statement that converted the date to a varchar format (as I suggested in my prior post).  I created the view, enabled it in Administrator, and added the entity (for the view) making it an extension of opportunity_product.  Then I added the date fields from this view to the column collection on the editable grid.  Works like a champ!  And I think it performs very well.

Highlighted
Employee
Posts: 51
Registered: ‎12-16-2011

Re: Date field in sData editable grid

If you look at the SmartParts\SalesOrder\SalesOrderProducts.js script we build up an array of columns. One of the attributes is "type". This is not exposed in AA for 7.5.4, but we do have a DateTime column type available in the source. So you could set that column to:

 

type: Sage.UI.Columns.DateTime

 

 

Make sure to do a dojo.require on it: 

dojo.require('Sage.UI.Columns.DateTime');

 

Unfortunately this script is generated in AA, so you would lose the changes on a re-deploy. Adding a Date/Time column is supported in 8.0

 

Highlighted
Copper Contributor
Posts: 31
Registered: ‎04-08-2010

Re: Date field in sData editable grid

Thanks for the suggestion.  I'll check it out.  We had determined that our approach of using a SQL view was not acceptable.  It killed the ability to delete the opportunity product.  We had defined the view as an extension to OpportunityProduct, so that the dates would be associated to the correct opportunityproductids.  However, the SData delete failed, since it can't delete properties from the view.  Dah! 

 

In our scenario, startdate and enddate are two date fields added to products.  They are "default dates" for educational classes (the product).  Adding the product to the opportunity carries those default dates forward, and then a user can edit the dates on the edit opportunity product view.  So, we had two primary challenges.  We added the dates to the Relate OnSelection script, and it returned a "/date..." string field, which displayed in the grid.  Then we also needed to convert a real date field to string when we edited the date with Edit Opportunity Product.

 

By the time I read your post, we had almost finished another approach.  We tried using the suggestion of calculated fields, but we could not update the fields with script, since they were read-only.  Maybe there's a way around that, but we chose to create "real fields" (string format) and update them. 

1.  We updated all existing records with a one-time SQL update script. 

2.  Then, we created a business rule for opportunityproduct.  The rule is called by the EditOpportunityProduct view OnChange Action for both date fields. 

3.  Then we added script (js) to the OpportunityProducts grid Relate OnSelection script.  This script had to snip text off of the SData-returned date value, convert the value to a date and then format it.

 

So far, it seems to work well and perform well.  And everything is exposed in AA (although was difficult to figure out).  I'm glad to hear that a date/time column will be supported in 8.0.