locked
Using Workflow to lookup data from one table and populate result into a table RRS feed

  • Question

  • I have a customised entity called Monthly Output Profile (my source table) which reads like this: 

    Month  No          Start                      End                                        Output Name                    Target Number

    1                              1 April 09              30 April 09                           Workshops                         3

    2                              1 May 09              31 May 09                            Workshops                         4

    3                              1 June 09             10 June 09                           Workshops                         2

    Etc. for 18 months.   This is inputted all in one batch on 1st April 09 and never again altered.

     

    I then have to input on a monthly basis my Actual Outputs for that Month just passed and I input on another form “Evidence for Output”, the Workshop details and the date the workshop was run, which looks like this.

    Date of Output                 Output Name                    Actual Number

    16 April 09                           Workshops                                         1

    17 April 09                           Workshops                                         1

    4 Jun 09                                Workshops                                         1

    At a later stage I will run a report that would look like this:

    Month  No          Output Name                    Target Number                 Actual Number

    1                              Workshops                         3                                                              2

    2                              Workshops                         4                                                              0

    3                              Workshops                         2                                                              1

    TOTAL                                                                   9                                                              3

    ...which basically brings the two tables together linked by Month No and compares what was profiled for the Month and what was actually achieved.   

    In order to get these two tables to link up nicely in one report, I need to populate into my “Evidence for Output” table the Month Number as found on the “Monthly Output Profile” -   so for the record dates 16th and 17th April the Month Number is 1, whilst for the date 4 Jun 09 the Month Number is 3.

    I’m simplifying the problem somewhat for brevity and clarity, as I’m happy with the integrity of my approach with unique IDs and primary keys.

    I would expect I need to run a workflow on the saving of the “Evidence for Output” that looks for the Month Number from the first table (Monthly Output Profile) that corresponds for the date of the Evidence record and then populates a field (Month No) into the “Evidence for Output” table.

    I’ve a reasonable (gifted amateur) understanding of workflows and would be grateful on views as to:

    1)      whether this approach is possible using a workflow, if someone like me (gifted amateur) could do it and if there are any comparable samples) I could follow to get me going.

    2)      Any other alternatives that may work.

    Thanks in anticipation

     

    Jim

    Friday, May 1, 2009 5:50 PM

Answers

  • Jim - 

    From what you are describing there is no way to do this with out of the box workflow without writting custom workflow extensions to do the lookup and association.

    You have a few options to enforce or capture this within CRM
    1.  Create a relationships from the Actual Outputs and require the user to select the month it is in relationship to when they create the record.  Then if you are worried that soem users will enter bad data either build a Plug-in to stop them from saving bad data or build a report to show any missmatches when the data is created.
    2.   Build a plugin that does the searh for the month on create and update of the Actual Outputs to fill in the correct month.
    3.   Build a worlkflow extension that will look up and get the correct month. Use it within the workflow on Actual Outputs to fill in teh correct month.
    4.   Build the logic into your report and never store the link but use the dates to build the report.  Only problem here is that if you over time build up lots of data this will slowly take a long time to run basing your linking on dates.

    I hope that helps

    Later
        Hoss
    • Marked as answer by Jim Glass Jr Friday, May 1, 2009 8:22 PM
    Friday, May 1, 2009 7:58 PM

All replies

  • Jim -

    How about some jscript on the 2 forms that stores the value of the month in a separate field?

    Assuming your current date field on both forms is named "new_startdate" (adjust the script as needed to match your field name)

    Add a new attribute "new_month" as an integer field on both entities

    In the form Editor for the on_change of the existing StartDate field, add:

    var startDate = crmForm.all.new_startdate.DataValue;
    //Check to see if the start date is not null first.
    if (startDate)
    {
    crmForm.all.new_month.DataValue = startDate.getMonth()+1;
    }

    This will populate the 'new_month' field with the numeric value of the month in the startDate field. (BTW the dates are numbered 0-11 in CRM so we add 1 to it to get the standard 1-12)

    What do you think?


    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Friday, May 1, 2009 7:28 PM
    Moderator
  • Jim - 

    From what you are describing there is no way to do this with out of the box workflow without writting custom workflow extensions to do the lookup and association.

    You have a few options to enforce or capture this within CRM
    1.  Create a relationships from the Actual Outputs and require the user to select the month it is in relationship to when they create the record.  Then if you are worried that soem users will enter bad data either build a Plug-in to stop them from saving bad data or build a report to show any missmatches when the data is created.
    2.   Build a plugin that does the searh for the month on create and update of the Actual Outputs to fill in the correct month.
    3.   Build a worlkflow extension that will look up and get the correct month. Use it within the workflow on Actual Outputs to fill in teh correct month.
    4.   Build the logic into your report and never store the link but use the dates to build the report.  Only problem here is that if you over time build up lots of data this will slowly take a long time to run basing your linking on dates.

    I hope that helps

    Later
        Hoss
    • Marked as answer by Jim Glass Jr Friday, May 1, 2009 8:22 PM
    Friday, May 1, 2009 7:58 PM