How to include sales data in the appointment data table (dAppointment)

1 post / 0 new
Erik
Erik's picture
How to include sales data in the appointment data table (dAppointment)

As you probably know is's easy to refer a sale when you create an appointment. This is usefull when you want to visualize that this appointment is directly related to a specific sale. It looks like this:

I'm thinking that I need these new columns:

  1. An owl link to give me direct access to the sale dialog
  2. The title of the sale
  3. The amount of the sale

First I go about how to create the SQL statement that gets me what I want. I'll be using SQL Server Managment studio and SQL server Profiler for that. I capture a SQL statement using the already existing dAppointment data table and try to modify it.

I ended ut adding these lines of code (copy & paste convenience):

-------------------------------------------------------------------------------
-- CUSTOM SECTION START: SALE DATA IN APPOINTMENT DATA TABLE
-------------------------------------------------------------------------------
,case
    when app.sale_id != 0 then (select sale_id from crm7.sale where sale_id = app.sale_id)
    else ''
  end as [Sale URL]

 ,case
    when app.sale_id != 0 then (select heading from crm7.sale where sale_id = app.sale_id)
    else ''
  end as [Sale title]

 ,case
    when app.sale_id != 0 then (select
                                  s.amount * =dv(dbowner);.ba_cur_conv4(cur.rate, cur.units, =param("id": "e461176b-2702-4b19-a4d1-e26b4e50cd2a");)
                                from
                                  crm7.sale s
                                  join crm7.currency cur on cur.currency_id = s.currency_id -- CURRENCY MUST BE ENABLED!
                                where
                                  s.sale_id = app.sale_id)
    else 0.0
  end as [Sale amount]

-------------------------------------------------------------------------------
-- CUSTOM SECTION END: SALE DATA IN APPOINTMENT DATA TABLE
-------------------------------------------------------------------------------

I pasted the new lines into the SQL definition for dAppointment in the Data central, and it looked like this:

That's fine but the owl link column needs a bit more work. Click the Columns tab and locate the new column that we called Sale URL. Click the expression button, and the expression dialog appears. It looks like this:

I then go ahead and enter an expression to make the magic happen:

The expression for your copy & paste convenience:

=eval(if('[Sale URL]' > 0) {"=solink("dialog":"sale","id":"[Sale URL]","target":"_blank");"} else {''});

Notice that this expression became a bit more complex that usual when crreating an owl link. That is because not all appointments refer a sale. Therefore we only want to display the owl link when a sale is refered. I added a bit of javascipt logic to handle that. Otherwise we could have used this expression:

=solink("dialog":"sale","id":"[Sale URL]","target":"_blank");

We are done, and can click Save. But if we want to explore and test expressions, we can click the button labeled Edit Scipt expression.

If we do that we get a new dialog that looks like this (one can have a lot of fun here but thats another topic. I included the screen shot anyway...

You mat click the preview tab so see the new columns working.

Click the Save button to make the changes to the dAppointment data table permanent. We're done!

You may now include the new columns using the grouping dialog. Like this:

Simply check the columns you want to include. Thats it for now!