AX 2009 - creating a job to update a database field

I created the job using a basic SQL query to pull a record and then a method attached to that table that just added a defined variable amount to a date.

But that only did one record…   apparently I forgot the loop.  That was easy, just add while to the beginning of the select statement.

//ATLAS, Joseph Anderson - 03/14/2012 - J0001

static void ATL_jbAdjustRouteOprToDate(Args _args)

{

    ProdRoute pr;

    int idx = 0;

    ;

    ttsBegin;

        while select forUpdate pr

            where pr.OprFinished==0

        {

            pr.ATL_AdjustOprToDate = pr.AdjustToDate();

            pr.doUpdate();

            idx++;

        }

    ttsCommit;

//    update_recordset pr

//    setting

//        ATL_AdjustOprToDate = pr.AdjustToDate()

//    where OprFinished == 0;

    info(strFmt("Number of records updated is %1.", idx));

}

AX 2009 - Adding a calculated field to a grid

The first time I did this, I over-complicated it.  I wasn’t sure how to write a complicated SQL query so I create a Query object, then a View object that saw that query, and then a data Method that pulled that “record” and a display Method that pushed the field to the grid.

Yeah, over-complicated.

Here is a cleaner way to do it.

On the primary table for the form, add a method that generates the value.

In the Design of the Form, add an appropriate field.

  In this case it was a Date field.

Set the DataSource to the table, and instead of assigning a DataField you assign the DataMethod to the method you wrote.

Unfortunately, a calculated field can’t be sorted by or filtered by, so I have to build it directly into the table (third time is a charm!)

sql int to time

I found this thread and thought it was worth repeating.  My email-to-blog had a link to the original posts, but apparently it didn't come through.

Original Post:  I have stored, as seconds, a duration in a table. I would like to use a SELECT statement to retrieve the contents of the column but display them as a time. For example:

45 = 0:00:45
241 = 0:04:01
575 = 0:09:35

and so on...

---

Answer:

declare @seconds int
set @seconds = 241
select convert(char(8), dateadd(second, @seconds, ''), 114)

---

Response:

Thanks for this.
  I took your example and turned it in to this which works:

SELECT CONVERT(char(8), DATEADD(second, Duration, ''), 114) AS Duration