Clarizen connector Query in Jitterbit Design Studio
The Clarizen query activity available within the Clarizen connector allows you to search for existing data in a connected Clarizen instance using Jitterbit. Jitterbit provides a wizard to build the query, or you can choose to build your own searches using Clarizen Query Language (CZQL).
Note
Before you set up a Clarizen query operation, you will need to have a Clarizen endpoint defined. For more information on creating an endpoint, see Clarizen connector endpoint.
This page describes how to search for existing Clarizen data using an example of querying an existing task in Clarizen through Jitterbit. The same task being queried was used in the examples for Clarizen connector create and Clarizen connector update.
Example
For reference, see the Jitterpak ClarizenExample.jpk and accompanying files in ClarizenSampleFiles.zip. Unzip the ClarizenSampleFiles.zip to your "C:\" drive, or if you unzip to another directory make sure to edit the source and target directories in the operations. If this is your first time using a Jitterpak see Importing a Jitterpak.
This example is used for demonstration purposes only and does not cover all options available in the product; please refer to Design Studio for more comprehensive documentation.
The following sections are divided up into beginning the query activity, defining the operation components, and deploying and executing the operation.
Creating a Clarizen Query operation
Note
If you are not already familiar with Jitterbit, see Get started or Design Studio for detailed information on how to use the product.
-
Within your project within Jitterbit Studio, there are several ways to start a new Clarizen query activity.
- Go to File > New > Connectors > New Clarizen Query.
- In the tree on the left under Connectors, right-click on the Clarizen category, then select New Clarizen Query.
- In the tree on the left under Connectors, double-click on the Clarizen category, then right-click on Clarizen Query Activities and select New Clarizen Query.
- In the top toolbar, click the connector icon (orange jigsaw piece)
. In the popup, select Clarizen, then select Clarizen Query.
-
The endpoint configuration screen should open directly in the main view of Studio. Select the endpoint that you would like to search for existing data in. You should have already set up your endpoint in Clarizen connector endpoint. Click Next when finished.
-
The object configuration screen should open. Select the object that you would like to search for existing data in. In this example we want to search for a task within our Clarizen instance, so we select the "Task" object. Click Next to continue.
Note
If you have a lot of objects available in your Clarizen instance, you may need to wait a moment for them to load. You can also enter an object name into the Filter field. Try the Refresh button if the results are not what you expect.
-
The query configuration screen should open. The following screenshot displays the example configuration, with configuration options explained below.
-
Type of Query : In the pane on the left are two tabs that allow you to select either Simple Query or Relationship Query. In the example, we will select the Simple Query tab and click the Select All button to return all fields within the task object. Each type of query is described below.
-
Simple Query: A simple query allows you to select from fields within the object you selected on the previous screen. As you select fields, they will be added to the Query string in the right pane.
-
Relationship Query: A relationship query allows you to select from fields within the object, as well as to specify parent or child relationships. You can view the fields within each parent or child by double-clicking on the item. You can also get back to the previous view using the breadcrumbs that appear above the field list. As you create a query, the Query string will populate in the right pane. Relationship queries are defined with a "." between the object and field names.
Note
If you are already familiar with Clarizen Query Language (CZQL), you can also input the query manually by placing it directly in the Query string box. For further information and examples, see Clarizen's REST API Reference Guide.
-
-
Conditions: In the top right, you can add conditions to limit your search. For the example, use the Field dropdown to select 'ExternalID', set the Operator to 'equals', and enter the value of the external ID in Clarizen (e.g. 'jw47joqmno25rh7l5azcmo3o26' from the example from Clarizen connector create). Then click Add Condition and scroll down in your Query string to make sure the condition has been added.
-
Paging: Use these settings only if you want to limit the number of records returned or the offset. In the example we are only returning 1 record, so we do not want to apply any paging. To use paging, you can adjust the number of records returned (maximum of 1000), and optionally apply an offset.
For example, if you set the number of records to 500 with no offset, the first 500 rows will be returned. If you then perform another query and apply an offset of 500, then the query will return records starting at record 501. Note that you must click Apply in order to use this option, and you will see it added to the Query string below.
-
Query string: As you add fields and conditions, the query string should automatically populate here. You can also edit the query string directly using CZQL to include a manual query. It's often a good idea to check your query string after you add fields and conditions so you can see the query that has been created.
-
Validate Query: Check this box to validate the query, if the query was built by checking the field boxes and adding conditions via this page. If you inserted a query string manually, this option may not work as expected.
-
Test Query: Use this button to submit a test query prior to creating the operation. A message should indicate the results of the test. If successful, click Finish. If unsuccessful, check to make sure you have input a valid query and that your endpoint is connected.
Note
You also can manually modify any query by making changes within the Query string . This includes using global variables within WHERE causes. See Global variables for additional information.
-
-
-
Jitterbit will then proceed with creating a Clarizen API response structure that shows the structure of the response. A new tab should appear in Studio called Clarizen Query Activities. You can rename your query activity here if desired; in the example ours is called "Example Clarizen Search Task." If your query is long, like that shown in the example below, you may need to scroll down to see the response structure. You can use the + - buttons or arrows to expand all elements within the structure.
-
Next, on the same screen, click the button Create Operation located under Use in an Integration in the upper right corner. This will create the Jitterbit operation that will perform the search.
-
A new tab should open in Studio called Operations, containing a graphical representation of the search activity.
Note
The icons for Response and Target in the image above are placeholders for the operation components, which we will define in the next section.
Save your operation by clicking either the single disk icon to save just this operation or the multiple disk icon to save all changes in your integration project. You should see the asterisk on the Operations tab and operation title disappear as your new update activity is now created and saved to your project.
Defining the operation components
Next we need to define the other components of the operation that appear within the graphical representation, including Response and Target.
For each, the instructions below direct you to return to the graphical representation of the operation you have created and double-click each icon to configure each component. As an alternative, you could also create the Response and Target separately and then drag and drop them from the tree on the left directly onto the icons in the visual representation of your operation.
For purposes of this walkthrough, we provide the following examples for reference above: Jitterpak ClarizenExample.jpk and accompanying files in ClarizenSampleFiles.zip. Many different types of data can be used for each component of the operation. To learn more about additional customization options, refer to the Design Studio section of our documentation.
-
Target: Double-click on the Target icon. In the example, we will record the query response from Clarizen to an XML file. Click the Create New Target button, give your target a Name (e.g. ClarizenQuery_Task_Response.xml). For this example, we are using a private agent with local files enabled, and thus select a Type of "Local File" and then Browse to the location where we want to save the data. In the example, the Folder is 'C:\ClarizenSampleFiles' and the Filename is 'ClarizenQuery_Task_Response.xml'.
Note
You can also output to a variety of different target types. The type does not have to be the same as the source type. See Targets for options.
-
Response: Double-click on the Response icon and select Create New Transformation. This will open the Transformation Wizard which will walk you through creating the response.
Note
Additional information on response structures is provided under Transformations.
-
On the first screen, Name, provide a Name for your transformation or leave as the default. Then use the Target dropdown to select "Clarizen Function Response." Click Next to continue.
-
On the next screen, Target, select the radio button for Query, then click Next. On the following screen, use the dropdown to select the query operation (e.g. "Example Clarizen Search Task"), then click Finish.
-
The Transformations tab should open where you can complete your mapping. Use the + to expand the source and target sides. In this example we will create the output response so that it includes all of the queried fields in the response. Click and drag the "OUTPUT" folder from the left side (source side) to the "OUTPUT" folder on the right side (target side). Your mapping screen should now look similar to that below.
Note
If you need to modify a query that has existing transformations, click the green refresh button
to refresh the transformation to include or remove fields as necessary.
-
Deploying and executing the operation
With the Clarizen query activity fully configured, we are ready to deploy and execute the operation.
-
From the Operations tab, click the deploy
icon. -
Then click the execute
icon to place the operation in the queue for execution. -
In the lower portion of the screen, the Operation monitor should indicate that your operation was run successfully. If not, you can double-click the Status icon to view any log messages.
-
You should also now see the output response that was mapped to your target file. In the example, the file is now created in 'C:\NetSuiteSampleFiles\ClarizenQuery_Task_Response.xml' and includes all the task fields for the task with the specific ID that was queried.
<ns:OUTPUT xmlns:ns="urn:czoln-res:document:czoln:oln:entities"> <Task> <Entity> <id>/Task/jw47joqmno25rh7l5azcmo3o26</id> <ActiveStopwatch> <id>/StopwatchAggregateState/None</id> </ActiveStopwatch> <ActualBilledExpenses/> <ActualCost/> <ActualDuration/> <ActualEffort> <unit>Hours</unit> <value>0</value> </ActualEffort> <ActualEffortUpdatedFromTimesheets>1</ActualEffortUpdatedFromTimesheets> <ActualExpenses/> <ActualRevenue/> <ActualStartDate>2016-12-13 06:00:00</ActualStartDate> <AggregatedStopwatchesCount>0</AggregatedStopwatchesCount> <AllResourcesCount>1</AllResourcesCount> <AllowReportingOnSubItems>1</AllowReportingOnSubItems> <AttachmentsCount>0</AttachmentsCount> <BaselineCost/> <BaselineCostsVariance/> <BaselineDueDateVariance/> <BaselineDuration/> <BaselineDurationVariance/> <BaselineRevenue/> <BaselineRevenueVariance/> <BaselineStartDateVariance/> <BaselineWork/> <BaselineWorkVariance/> <Billable>1</Billable> <BudgetStatus/> <BudgetedHours/> <BudgetedHoursManuallySet>0</BudgetedHoursManuallySet> <C_Activity/> <C_ActualEffortinHours>0</C_ActualEffortinHours> <C_Actual_QA_Work/> <C_AmountofbugsinHFAppIntegration>0</C_AmountofbugsinHFAppIntegration> <C_ApplicationStatus/> <C_ApproveCR>https://app2.clarizen.com/Clarizen/CustomOperation?caid=277.46580865.21&target=8.7198804673.21</C_ApproveCR> <C_Areapath/> <C_AutoEEScale/> <C_AvailableIterationPaths/> <C_BEEEScale/> <C_BRDStatus/> <C_BackOfficeTask>0</C_BackOfficeTask> <C_Backlog> <id>/C_WorkItemBacklog/No</id> </C_Backlog> <C_BelongtoPortfolio/> <C_BestPracticeType/> <C_CLZ_TeamBoardScrumMaster_CLZ/> <C_CLZ_TeamBoardState_CLZ> <id>/C_WorkItemCLZ_TeamBoardState_CLZ/Backlog</id> </C_CLZ_TeamBoardState_CLZ> <C_CLZ_TeamBoardStatusIcon_CLZ/> <C_CLZ_TeamBoardType_CLZ> <id>/C_WorkItemCLZ_TeamBoardType_CLZ/Task</id> </C_CLZ_TeamBoardType_CLZ> <C_CLZ_TeamTaskColor_CLZ> <id>/C_WorkItemCLZ_TeamTaskColor_CLZ/Blue</id> </C_CLZ_TeamTaskColor_CLZ> <C_CSCustomer/> <C_CSManager/> <C_CaseComponent/> <C_ClosedBugsCount>0</C_ClosedBugsCount> <C_CodeComplete> <id>/C_WorkItemCodeComplete/No</id> </C_CodeComplete> <C_ComplianceNextStep>https://app2.clarizen.com/Clarizen/CustomOperation?caid=277.38111041.21&target=8.7198804673.21</C_ComplianceNextStep> <C_CreateWorkItemInJIRA>0</C_CreateWorkItemInJIRA> <C_Customer/> <C_Customertask>0</C_Customertask> <C_DeploymentStatus/> <C_DevBugsCount>0</C_DevBugsCount> <C_Done> <id>/C_WorkItemDone/No</id> </C_Done> <C_ExpectedLag>0</C_ExpectedLag> <C_FEEEScale/> <C_FromCR>0</C_FromCR> <C_GRCEstimatedEffort>0.5</C_GRCEstimatedEffort> <C_GRCReviewed>0</C_GRCReviewed> <C_Geo_Flag> <id>/C_WorkItemGeo_Flag/Orange</id> </C_Geo_Flag> <C_Group/> <C_HoursComplete>0</C_HoursComplete> <C_ITCREnvironment/> <C_ITCRManager/> <C_ITCRServiceNotification>0</C_ITCRServiceNotification> <C_ITCRSite/> <C_ITCRState/> <C_InProgress> <id>/C_WorkItemInProgress/No</id> </C_InProgress> <C_IncidentComponent/> <C_IncidentPublicFlag>0</C_IncidentPublicFlag> <C_IncidentState/> <C_IncidentType/> <C_Incidentready>0</C_Incidentready> <C_IncludedinImplementation/> <C_Interact>mailto:T-1425955@tickets.clarizenmail.com</C_Interact> <C_InvestmentReason/> <C_Iteration> <id>/C_WorkItemIteration/0</id> </C_Iteration> <C_JIRAAssignee/> <C_JIRAEpicTopicRef/> <C_JIRAV2IssueType> <id>/C_GenericTaskJIRAV2IssueType/Task</id> </C_JIRAV2IssueType> <C_ManagedServicesWI>0</C_ManagedServicesWI> <C_MarketingHighlight>0</C_MarketingHighlight> <C_MultiSiteTask>0</C_MultiSiteTask> <C_NumberofRegressionbugsAppIntegration>0</C_NumberofRegressionbugsAppIntegration> <C_OpenIssue>0</C_OpenIssue> <C_OriginalParent/> <C_PPR_ActualEndDateWithinRange>0</C_PPR_ActualEndDateWithinRange> <C_PPR_DueDateWithinRange>0</C_PPR_DueDateWithinRange> <C_PPR_NamebyStatus><span style='color: green;font-size:10;'>Testing 123</span></C_PPR_NamebyStatus> <C_PPR_OffTrackorAtRisk>0</C_PPR_OffTrackorAtRisk> <C_PPR_StateImage>https://www.clarizen.com/SecureAppImages/clarizen_icons/state_.png</C_PPR_StateImage> <C_PPR_StatusImage>https://www.clarizen.com/SecureAppImages/clarizen_icons/status_green.png</C_PPR_StatusImage> <C_PSCommittedHours>0</C_PSCommittedHours> <C_PSProjectPhase/> <C_PSReportedHours>0</C_PSReportedHours> <C_PSTeamLeader/> <C_PSTotalReportedHours/> <C_ProductAction2>https://app2.clarizen.com/Clarizen/CustomOperation?caid=277.40833665.21&target=8.7198804673.21</C_ProductAction2> <C_ProductDefinition/> <C_ProductFitPL/> <C_ProductManager/> <C_ProductMockup/> <C_Project/> <C_ProjectPriority>1</C_ProjectPriority> <C_QABugsCount>0</C_QABugsCount> <C_QAEEScale/> <C_RDActivityLeader/> <C_RDAutomationDocumentStatus> <id>/C_WorkItemRDAutomationDocumentStatus/Not Started</id> </C_RDAutomationDocumentStatus> <C_RDAutomationState> <id>/C_WorkItemRDAutomationState/Not started</id> </C_RDAutomationState> <C_RFPApproval/> <C_RFPCustomer/> <C_RFPCustomerSegment/> <C_RFPNDASigned>0</C_RFPNDASigned> <C_RFPRealisticExpectedARR/> <C_RFPRequestType/> <C_RFPSalesReion/> <C_RFPStage> <id>/C_GenericTaskRFPStage/Pending Approval</id> </C_RFPStage> <C_RejectCR>https://app2.clarizen.com/Clarizen/CustomOperation?caid=277.1895109.21&target=8.7198804673.21</C_RejectCR> <C_RemainingHoursNew>0</C_RemainingHoursNew> <C_RepetitiveIncident>0</C_RepetitiveIncident> <C_Report/> <C_Reporttime>N/A</C_Reporttime> <C_RequestedBy/> <C_RequiredSkillLevel/> <C_RoadmapEstimatedGA/> <C_RoadmapEstimatedGAMonth/> <C_RoadmapEstimatedPreviewAvailability/> <C_RoadmapNextPhase/> <C_RoadmapState/> <C_SalesRep/> <C_SeasonalRelease/> <C_Site/> <C_Skill/> <C_StorySizingScale/> <C_SundayActivity>0</C_SundayActivity> <C_Swimlane/> <C_SyncToClarizen/> <C_TS_AreaPath>projecTeam_Training</C_TS_AreaPath> <C_TS_ID>0</C_TS_ID> <C_TS_WorkItemType> <id>/C_WorkItemTS_WorkItemType/Task</id> </C_TS_WorkItemType> <C_Team/> <C_TicketCustomer/> <C_UTILCALC> <id>/C_WorkItemUTILCALC/Non-Billable</id> </C_UTILCALC> <C_UnderTest> <id>/C_WorkItemUnderTest/No</id> </C_UnderTest> <C_UpdateTFS> <id>/C_WorkItemUpdateTFS/Yes</id> </C_UpdateTFS> <C_UpdateonScheduleChange>0</C_UpdateonScheduleChange> <C_UtilizationCategory> <id>/C_WorkItemUtilizationCategory/Holiday</id> </C_UtilizationCategory> <C_Visibletocustomer>1</C_Visibletocustomer> <C_WorkHours>0</C_WorkHours> <C_WorkItemOwner> <id>/User/61vsczn8ftsuekp54c5x7vcfg8224</id> </C_WorkItemOwner> <C_movecustomaction>0</C_movecustomaction> <C_triggerITCRpost>0</C_triggerITCRpost> <CalculateCompletenessBasedOnEfforts>1</CalculateCompletenessBasedOnEfforts> <Charged> <id>/ChargedType/Not charged</id> </Charged> <ChargedAmount/> <ChargedTypeManuallySet>0</ChargedTypeManuallySet> <ChildShortcutCount>0</ChildShortcutCount> <ChildrenCount>0</ChildrenCount> <CommitLevel> <id>/CommitLevel/No Commit</id> </CommitLevel> <CompletnessDefinition>0</CompletnessDefinition> <Conflicts>0</Conflicts> <CostBalance/> <CostCurrencyType/> <CostVariance/> <Country/> <CreatedBy> <id>/User/61vsczn8ftsuekp54c5x7vcfg8224</id> </CreatedBy> <CurrencyEAC/> <CurrencyETC/> <CurrencyREAC> <currency>USD</currency> <value>0</value> </CurrencyREAC> <CurrencyRETC/> <Deliverable>1</Deliverable> <Description>This is my updated task.</Description> <DirectActualBilledExpenses/> <DirectActualExpenses/> <DirectPlannedBilledExpenses/> <DirectPlannedExpenses/> <DirectProjectedBilledExpenses/> <DirectProjectedExpenses/> <DueDateVariance/> <Duration> <unit>Days</unit> <value>3</value> </Duration> <DurationManuallySet>1</DurationManuallySet> <DurationVariance/> <EarnedValue/> <EmailsCount>0</EmailsCount> <Executable>1</Executable> <ExpectedProgress>0</ExpectedProgress> <ExternalID>jw47joqmno25rh7l5azcmo3o26</ExternalID> <FixedCost/> <FixedPrice/> <FloatingTask>1</FloatingTask> <GeographicalRegion/> <ImageUrl>https://app2.clarizen.com/Clarizen/Media/35.490797.0-087FF95A68A016AFD27BD6A7E0E9B90C41C15789/Image.gif</ImageUrl> <Importance> <id>/Importance/Normal</id> </Importance> <ImportedFrom> <id>/ImportedFrom/None</id> </ImportedFrom> <IndividualReporting>0</IndividualReporting> <InstanceNumber>0</InstanceNumber> <IssuesCount>0</IssuesCount> <LastUpdatedBy> <id>/User/61vsczn8ftsuekp54c5x7vcfg8224</id> </LastUpdatedBy> <LikesCount>0</LikesCount> <Manager> <id>/User/61vsczn8ftsuekp54c5x7vcfg8224</id> </Manager> <Milestone/> <Name>Testing 123</Name> <NotesCount>0</NotesCount> <OnCriticalPath>0</OnCriticalPath> <Parent/> <ParentProject/> <Pending> <id>/Pending/None</id> </Pending> <PendingTimeTrackingEffort> <unit>Hours</unit> <value>0</value> </PendingTimeTrackingEffort> <PercentCompleted>0</PercentCompleted> <Phase> <id>/Phase/Roll-out</id> </Phase> <PlannedAmount/> <PlannedBilledExpenses/> <PlannedBudget/> <PlannedExpenses/> <PlannedRevenue/> <PostsCount>0</PostsCount> <PredecessorsCount>0</PredecessorsCount> <Priority>500</Priority> <Profitability/> <Project/> <ProjectedBilledExpenses/> <ProjectedExpenses/> <RTCPI>0</RTCPI> <RemainingEffort> <unit>Hours</unit> <value>0</value> </RemainingEffort> <RemainingEffortManuallySet>0</RemainingEffortManuallySet> <Reportable>0</Reportable> <ReportableManuallySet>0</ReportableManuallySet> <ResourceUtilizationCategory/> <ResourcesCount>0</ResourcesCount> <RevenueBalance/> <RevenueCurrencyType/> <RevenueEarnedValue/> <SYSID>T-1425955</SYSID> <SchedulingType> <id>/SchedulingType/As Soon As Possible</id> </SchedulingType> <SetByLeveling>0</SetByLeveling> <StartDateVariance/> <State> <id>/State/Active</id> </State> <StateProvince/> <StopwatchesCount>0</StopwatchesCount> <SuccessorsCount>0</SuccessorsCount> <TaskReportingPolicy> <id>/TaskReportingPolicy/Shared</id> </TaskReportingPolicy> <TaskReportingPolicyManuallySet>0</TaskReportingPolicyManuallySet> <TaskType> <id>/TaskType/General</id> </TaskType> <TimeTrackingBilling/> <TimeTrackingCost/> <TimeTrackingEffort> <unit>Hours</unit> <value>0</value> </TimeTrackingEffort> <TotalEstimatedCost/> <TrackStatus> <id>/TrackStatus/On Track</id> </TrackStatus> <TrackStatusManuallySet>0</TrackStatusManuallySet> <Work/> <WorkManuallySet>0</WorkManuallySet> <WorkPolicy> <id>/WorkPolicy/Fixed Work</id> </WorkPolicy> <WorkVariance/> </Entity> <status>1</status> </Task> </ns:OUTPUT>