/
Microsoft Dynamics Client and Jobs Integration - Reference

Microsoft Dynamics Client and Jobs Integration - Reference

Owned by Matthew Garrett
Last updated: Aug 24, 2021
7 min read


Return to Microsoft Dynamics Client and Jobs Integration
Article Index

Integrated Fields

The integration of Microsoft Dynamics Client and Jobs to Framework ECM is fully customisable via the integration mapping database.

Dbo.FramProj

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



JobPkid



Yes

No

Framework - l_job_id



contextID



Yes

Yes

Special #37: Framework - l_context_id or ini profile Office Context Value.



ProjID



Yes

Yes

Framework - s_job_num



CustName



Yes

Yes

Framework - s_clientName



ProjStatus



Yes

Yes

Framework - s_jobStatus



CustStreetNum



Yes

Yes

Framework - s_clientStreetNum



CustStreetName



Yes

Yes

Framework - s_clientStreetName



CustCity



Yes

Yes

Framework - s_clientSuburb



CustPostcode



Yes

Yes

Framework - s_clientPostcode



SaleType



Yes

Yes

Framework - l_prd_sType_id



StageMajorID



Yes

Yes

Framework - s_wflStgMajorName



ProjStreetName



Yes

Yes

Framework - s_jobStreetName



ProjCity



Yes

Yes

Framework - s_jobSuburb



ProjPostCode



Yes

Yes

Framework - s_jobPostcode



ConsRegionID



Yes

Yes

Framework - s_teamName



ConsPromotionTypeID



Yes

Yes

Framework - l_prd_prom_id



ConsLotID



Yes

Yes

Framework - s_jobLotNum



ConsHouseNumber



Yes

Yes

Framework - s_jobStreetNum



ConsStoreyID



Yes

Yes

Framework - l_prd_hElev_id



ConsBuildingTypeID



Yes

Yes

Framework - l_prd_hType_id



ConsSeries



Yes

Yes

Framework - l_prd_hSeries_id



ConsFacadeID



Yes

Yes

Framework - l_prd_hFacade_id



ConsConstructionManagerID



Yes

Yes

Framework - s_csManager



ConsSupervisorID



Yes

Yes

Framework - s_supervisorName



ConsPreSiteAdminID



Yes

Yes

Framework - s_adminName



ConsConstructionAdminID



Yes

Yes

Framework - s_csgAdminName



ConsSalesPersonID



Yes

Yes

Framework - s_salesPersonName



ConsOrderingEstimatorID



Yes

Yes

Framework - s_estimatorName



ConsContractDraftpersonID



Yes

Yes

Framework - s_draftsPersonName



ConsTenderEstimator



Yes

Yes

Framework - s_tenderEstimatorName



ConsBaseHousePrice



Yes

Yes

Framework - c_retail_internal



ConsContractValue



Yes

Yes

Framework - c_retail_external



ConsMapRefID



Yes

Yes

Framework - s_jobMapRef



ResponsibleParty



Yes

Yes

Framework - s_rPartyName



ActSiteStartDate



Yes

Yes

Framework - d_site_start_act



ActDateCertOccRecv



Yes

Yes

Framework - d_cofo_fRec_act



ActSettlementDate



Yes

Yes

Framework - d_settle_act



CancellationCategory



Yes

Yes

Framework - s_cancelCategoryName



DateCancelled



Yes

Yes

Framework - d_cont_cancelled



CancellationNotes



Yes

Yes

Framework - s_notes_cancelled



DateContractDeleted



Yes

Yes

Framework - d_cont_deleted



DateJobCreated



Yes

Yes

Special #30: date job create & time date created.



JobCreatedBy



Yes

No

Framework - s_jobUserCreated



DateJobModified



Yes

No

Special #31: date job modified & time job modified.



JobModifiedBy



Yes

Yes

Framework - s_jobUserModified



ConsMunicipalityID



Yes

Yes

Framework - s_municipalityName



contractValueChangedBy



Yes

Yes





CustState



Yes

Yes

Framework - s_clientState



Dbo.FramProjContact

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



entityPkid



Yes

No

Framework - l_entity_id



contextID



Yes

Yes

Special #37: Framework - l_context_id or ini profile Office Context Value.



ProjID



Yes

Yes

Framework - s_job_num



Name



Yes

Yes

Framework - s_clientContName



PrimaryContact



Yes

Yes

Framework - f_primaryContact



ContactInactive



Yes

Yes

Framework - f_inactiveContact



BHTel



Yes

Yes

Framework - s_bhTel



AHTel



Yes

Yes

Framework - s_ahTel



Mobile



Yes

Yes

Framework - s_mobTel



Fax



Yes

Yes

Framework - s_faxTel



Email



Yes

Yes

Framework - s_emailTel



DateContactCreated



Yes

No

Special #32: date contact created & time contact created.



ContactCreatedBy



Yes

No

Framework - s_contUserCreated



ContactModifiedBy



Yes

Yes

Framework - s_contUserModified



DateContactModified



Yes

Yes

Special #33: date contact modified & time contact modified.



Dbo.FramProjVariation

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



DocPkid



Yes

No

Framework - l_doc_id



contextID



Yes

Yes

Special #37: Framework - l_context_id or ini profile Office Context Value.



ProjID



Yes

Yes

Framework - s_job_num



DocNumber



Yes

Yes

Framework - i_number



Amount



Yes

Yes

Framework - c_retail_total



DateSigned



Yes

Yes

Framework - d_signed_act



DocStatus



Yes

Yes

Framework - s_docStatus



DateVariationCreated



Yes

No

Special #34: date variation created & time variation created.



VariationCreatedBy



Yes

No

Framework - s_docUserCreated



DateVariationModified



Yes

Yes

Special #35: date variation modified & time variation modified.



VariationModifiedBy



Yes

Yes

Framework - s_docUserModified



Dbo.FwLUPrdHElev

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



PrdHElevPkid



Yes

No

Framework - l_prd_hElev_id



Inactive



Yes

Yes

Framework - f_inactive



Name



Yes

Yes

Framework - s_name



Dbo.FwLUPrdHFacade

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



PrdHFacadePkid



Yes

No

Framework - l_prd_hFacade_id



Inactive



Yes

Yes

Framework - f_inactive



Name



Yes

Yes

Framework - s_name



Dbo.FwLUPrdHSeries

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



PrdHSeriesPkid



Yes

No

Framework - l_prd_hSeries_id



Inactive



Yes

Yes

Framework - f_inactive



Name



Yes

Yes

Framework - s_name



Dbo.FwLUPrdHType

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



PrdHTypePkid



Yes

No

Framework - l_prd_hType_id



Inactive



Yes

Yes

Framework - f_inactive



Name



Yes

Yes

Framework - s_name



Dbo.FwLUPrdProm

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



PrdPromPkid



Yes

No

Framework - l_prd_prom_id



ContextID



Yes

Yes

Framework - l_context_id



Inactive



Yes

Yes

Framework - f_inactive



Name



Yes

Yes

Framework - s_name



Dbo.FwLUPrdSType

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



PrdSTypeID



Yes

No

Framework - l_prd_sType_id



Inactive



Yes

Yes

Framework - f_inactive



Name



Yes

Yes

Framework - s_name



Dbo.FwLURDiv

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Microsoft DynamicsDatabase Field Name

Microsoft Dynamics Field Label

Create

Update

Framework Database Field Name

Framework Field Label

SourceDatabase



Yes

No

Special #36: Framework Database Identifier. Preference default value retrieved from Framework.



contextID



Yes

No

Framework - l_context_id



Name



Yes

Yes

Framework - s_name



Inactive



Yes

Yes

Framework - f_inactive



Database Models

The following database model(s) displays the manner in which data is integrated between the third party application and Framework Integration.



 

Integration Process

The following information is a low level account of the integration process including criteria, decisions, and outcomes. This information is technical in nature and is provided "as-is" for a detailed analysis of the integration process for system administrators.

  1. Get Default Framework Database Identifier from Preference #-1024

  2. Integrate Jobs

  3.  

    1. Get all Framework Jobs from v_fwInt_dnmxJobs where

      1. If IncludeMaintenance is False

      2. job.l_wfl_stgMajor_id <> -5 (Maintenance)

      3. job.l_wfl_stgMinor_id <> -18 (Maintenance)

      4. If OrderCriteria is 1 (all jobs)

      5. Job.l_job_id <> 0 • Job.s_link_accts <> ‘N/A’ Else OrderCriteria is 2 (Single Job)

      6. Job.s_job_num = ‘OrderCriteriaID’

      7. Job.s_link_accts <> ‘N/A’

    2. We search for a match in Dynamics framProj table where

      1. framProj.jobPkid = job.l_job_id

      2. framProj.sourceDatabase = value for Framework Db Identifier

    3. Known issues that prevent a job from being inserted are

      1. that the Client name is null

      2. Jobs that don’t insert into the db could be in maintenance.

  4. Integrate Contacts

    1. Get all Framework Contacts from v_fwInt_dnmxContacts where

      1. Entity.l_entity_id <> 0

      2. Cont.d_pay_iDeposit_act is not null

      3. Job.s_link_accts <> ‘N/A’

      4. Job.l_wfl_stgMajor_id <> -5 (Maintenance)

      5. Job.l_wfl_stgMinor_id <> -18 (Maintenance)

    2. We search for a match in Dynamics FramProjContact table where

      1. FramProjContact.entityPkid = entity.l_entity_id

      2. FramProjContact.projID = job.s_job_num

      3. FramProjContact.sourceDatabase = value for Framework Db Identifier

    3. Known issues that prevent a contact from being inserted are

      1. that the contact name is null

  5. Integrate Variations

    1. Get all Framework Variations from v_fwInt_dnmxVarDocs where

      1. Doc.l_doc_id <> 0

      2. Cont.d_pay_iDeposit_act is not null

      3. Job.s_link_accts <> ‘N/A’

      4. Job.l_wfl_stgMajor_id <> -5 (Maintenance)

      5. Job.l_wfl_stgMinor_id <> -18 (Maintenance)

    2. We search for a match in Dynamics FramProjVariation table where

      1. FramProjVariation.docPkid = doc.l_doc_id

      2. FramProjVariation.sourceDatabase = value for Framework Db Identifier

    3. Known issues that prevent a variation from being inserted are

      1. The date field must be between 1/1/1753 and 31/12/9999

  6. Integrate other LookUp tables

    1. Framework Prd_sType updates fwLuPrdSType

    2. Framework Prd_hElev updates fwLUPrdHElev

    3. Framework Prd_hFacade updates fwLuPrdHFacade

    4. Framework Prd_hSeries updates fwLuPrdHSeries

    5. Framework Prd_hType updates fwLuPrdHType

    6. Framework prd_prom updates fwLuPrdProm

    7. Framework rDiv updates fwLuRDiv

    8. Where each table pkid matches and the sourceDatabase matches the Framework Db Identifier



When doing the integration, the program looks at job.l_rpt_index2 it gets the number entered in that field and it checks the ini profile for the key OfficeContext(x)=y where x is equal to the value in job.l_rpt_index2. If it finds this key then it puts the value of y into the contextID. If it does not find an officeContext setting for that value then it uses job.l_context_id and copies this value into the contextID fields. So if you change a jobs context identification, it will only change the contextID field if the ini profile has a key for that OfficeContext.

Here's an example Job #1 has a job.l_context_id of 1, but a job.l_rpt_index2 of 270 () If the ini profile has the key OfficeContext(270)=4 then when integration runs, it will set the FramProj.contextID for Job #1 to be 4. If it does not have the OfficeContext key then the FramProj.contextID will be set to the job.l_context_id value of 1. If you then change the context identifier for Job #1 to be (269) then rerun integration, if there is a key called OfficeContext(269)=2 then the FramProj.contextID will change to 2. If the OfficeContext key does not exist then the FramProj.contextId will change to 1. This is because the key couldn't be found so it uses the value in the job.l_context_id field instead.