I have created an advanced find view which I have exported to a dynamic worksheet. I have noticed that the 'Customer' and 'Resource' columns disappear when I export to a worksheet.
I have attempted to edit the query and include the customer however I cannot seem to get the correct results.
The original query was: SELECT serviceappointment.subject , serviceappointment.scheduledstart, serviceappointment.scheduledend, serviceappointment.regardingobjectidname , serviceappointment.activityid , serviceappointment.scheduleddurationminutes , serviceappointment.createdon , serviceappointment.serviceidname FROM FilteredServiceAppointment as serviceappointment ORDER by serviceappointment.subject asc
In trying to add the customer column, my new query is: SELECT serviceappointment.subject , serviceappointment.scheduledstart, activitypartycustomer.partyidname, serviceappointment.scheduledend, serviceappointment.regardingobjectidname , serviceappointment.activityid , serviceappointment.scheduleddurationminutes , serviceappointment.createdon , serviceappointment.serviceidname FROM FilteredServiceAppointment as serviceappointment INNER JOIN FilteredActivityParty as activitypartycustomer ON serviceappointment.activityid = activitypartycustomer.activityid ORDER by serviceappointment.subject asc
The problem is that this new query seems to return the resource and the customer in the same column. Would someone please assist me? I assume that I need to inlcude a WHERE statement and another INNER JOIN for the resource column.
> I have created an advanced find view which I have exported to a dynamic > worksheet. I have noticed that the 'Customer' and 'Resource' columns > disappear when I export to a worksheet.
> I have attempted to edit the query and include the customer however I cannot > seem to get the correct results.
> The original query was: > SELECT serviceappointment.subject , > serviceappointment.scheduledstart, > serviceappointment.scheduledend, > serviceappointment.regardingobjectidname , > serviceappointment.activityid , > serviceappointment.scheduleddurationminutes , > serviceappointment.createdon , > serviceappointment.serviceidname > FROM FilteredServiceAppointment as serviceappointment > ORDER by serviceappointment.subject asc
> In trying to add the customer column, my new query is: > SELECT serviceappointment.subject , > serviceappointment.scheduledstart, > activitypartycustomer.partyidname, > serviceappointment.scheduledend, > serviceappointment.regardingobjectidname , > serviceappointment.activityid , > serviceappointment.scheduleddurationminutes , > serviceappointment.createdon , > serviceappointment.serviceidname > FROM FilteredServiceAppointment as serviceappointment > INNER JOIN FilteredActivityParty as activitypartycustomer > ON serviceappointment.activityid = activitypartycustomer.activityid > ORDER by serviceappointment.subject asc
> The problem is that this new query seems to return the resource and the > customer > in the same column. Would someone please assist me? I assume that I need to > inlcude a WHERE statement and another INNER JOIN for the resource column.
> SELECT serviceappointment.subject , > serviceappointment.scheduledstart, > activitypartycustomer.partyidname, > resource.resourceid, > serviceappointment.scheduledend, > serviceappointment.regardingobjectidname , > serviceappointment.activityid , > serviceappointment.scheduleddurationminutes , > serviceappointment.createdon , > serviceappointment.serviceidname > FROM FilteredServiceAppointment as serviceappointment > INNER JOIN FilteredActivityParty as activitypartycustomer > ON serviceappointment.activityid = activitypartycustomer.activityid > INNER JOIN FilteredResource as resource > ON resource.resourceid = serviceappointment.activityid > ORDER by serviceappointment.subject asc
> This query was accepted however it did not return any data. I possibly > just > need to include a WHERE statement.
> Many Thanks > Mark
> "Mark Braithwaite" wrote:
>> Hi
>> I have created an advanced find view which I have exported to a dynamic >> worksheet. I have noticed that the 'Customer' and 'Resource' columns >> disappear when I export to a worksheet.
>> I have attempted to edit the query and include the customer however I >> cannot >> seem to get the correct results.
>> The original query was: >> SELECT serviceappointment.subject , >> serviceappointment.scheduledstart, >> serviceappointment.scheduledend, >> serviceappointment.regardingobjectidname , >> serviceappointment.activityid , >> serviceappointment.scheduleddurationminutes , >> serviceappointment.createdon , >> serviceappointment.serviceidname >> FROM FilteredServiceAppointment as serviceappointment >> ORDER by serviceappointment.subject asc
>> In trying to add the customer column, my new query is: >> SELECT serviceappointment.subject , >> serviceappointment.scheduledstart, >> activitypartycustomer.partyidname, >> serviceappointment.scheduledend, >> serviceappointment.regardingobjectidname , >> serviceappointment.activityid , >> serviceappointment.scheduleddurationminutes , >> serviceappointment.createdon , >> serviceappointment.serviceidname >> FROM FilteredServiceAppointment as serviceappointment >> INNER JOIN FilteredActivityParty as activitypartycustomer >> ON serviceappointment.activityid = activitypartycustomer.activityid >> ORDER by serviceappointment.subject asc
>> The problem is that this new query seems to return the resource and the >> customer >> in the same column. Would someone please assist me? I assume that I need >> to >> inlcude a WHERE statement and another INNER JOIN for the resource column.
> > SELECT serviceappointment.subject , > > serviceappointment.scheduledstart, > > activitypartycustomer.partyidname, > > resource.resourceid, > > serviceappointment.scheduledend, > > serviceappointment.regardingobjectidname , > > serviceappointment.activityid , > > serviceappointment.scheduleddurationminutes , > > serviceappointment.createdon , > > serviceappointment.serviceidname > > FROM FilteredServiceAppointment as serviceappointment > > INNER JOIN FilteredActivityParty as activitypartycustomer > > ON serviceappointment.activityid = activitypartycustomer.activityid > > INNER JOIN FilteredResource as resource > > ON resource.resourceid = serviceappointment.activityid > > ORDER by serviceappointment.subject asc
> > This query was accepted however it did not return any data. I possibly > > just > > need to include a WHERE statement.
> > Many Thanks > > Mark
> > "Mark Braithwaite" wrote:
> >> Hi
> >> I have created an advanced find view which I have exported to a dynamic > >> worksheet. I have noticed that the 'Customer' and 'Resource' columns > >> disappear when I export to a worksheet.
> >> I have attempted to edit the query and include the customer however I > >> cannot > >> seem to get the correct results.
> >> The original query was: > >> SELECT serviceappointment.subject , > >> serviceappointment.scheduledstart, > >> serviceappointment.scheduledend, > >> serviceappointment.regardingobjectidname , > >> serviceappointment.activityid , > >> serviceappointment.scheduleddurationminutes , > >> serviceappointment.createdon , > >> serviceappointment.serviceidname > >> FROM FilteredServiceAppointment as serviceappointment > >> ORDER by serviceappointment.subject asc
> >> In trying to add the customer column, my new query is: > >> SELECT serviceappointment.subject , > >> serviceappointment.scheduledstart, > >> activitypartycustomer.partyidname, > >> serviceappointment.scheduledend, > >> serviceappointment.regardingobjectidname , > >> serviceappointment.activityid , > >> serviceappointment.scheduleddurationminutes , > >> serviceappointment.createdon , > >> serviceappointment.serviceidname > >> FROM FilteredServiceAppointment as serviceappointment > >> INNER JOIN FilteredActivityParty as activitypartycustomer > >> ON serviceappointment.activityid = activitypartycustomer.activityid > >> ORDER by serviceappointment.subject asc
> >> The problem is that this new query seems to return the resource and the > >> customer > >> in the same column. Would someone please assist me? I assume that I need > >> to > >> inlcude a WHERE statement and another INNER JOIN for the resource column.
> >> I would really appreciate anyone's assistance.
Without looking into this - check the partytype (???) column, you should have different types for customer and resource -> partyidname. You will get multiple rows for each serviceappointment, with the different partytype in each row. To merge these you could either use filtered subquery for each column rather than table join OR join twice to the party table with an additional constraint of partytype in the ON clause and thereby separate the columns on one row. Hope this helps Chris http://mscrm4u.blogspot.com
