| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
Return to Microsoft Dynamics Client and Jobs Integration
Article Index
| Table of Contents |
|---|
Integrated Fields
The integration of Microsoft of Microsoft Dynamics Client and Jobs to Framework ECM is 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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 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.
Get Default Framework Database Identifier from Preference #-1024
Integrate Jobs
Get all Framework Jobs from v_fwInt_dnmxJobs where
If IncludeMaintenance is False
job.l_wfl_stgMajor_id <> -5 (Maintenance)
job.l_wfl_stgMinor_id <> -18 (Maintenance)
If OrderCriteria is 1 (all jobs)
Job.l_job_id <> 0 • Job.s_link_accts <> ‘N/A’ Else OrderCriteria is 2 (Single Job)
Job.s_job_num = ‘OrderCriteriaID’
Job.s_link_accts <> ‘N/A’
We search for a match in Dynamics framProj table where
framProj.jobPkid = job.l_job_id
framProj.sourceDatabase = value for Framework Db Identifier
Known issues that prevent a job from being inserted are
that the Client name is null
Jobs that don’t insert into the db could be in maintenance.
Integrate Contacts
Get all Framework Contacts from v_fwInt_dnmxContacts where
Entity.l_entity_id <> 0
Cont.d_pay_iDeposit_act is not null
Job.s_link_accts <> ‘N/A’
Job.l_wfl_stgMajor_id <> -5 (Maintenance)
Job.l_wfl_stgMinor_id <> -18 (Maintenance)
We search for a match in Dynamics FramProjContact table where
FramProjContact.entityPkid = entity.l_entity_id
FramProjContact.projID = job.s_job_num
FramProjContact.sourceDatabase = value for Framework Db Identifier
Known issues that prevent a contact from being inserted are
that the contact name is null
Integrate Variations
Get all Framework Variations from v_fwInt_dnmxVarDocs where
Doc.l_doc_id <> 0
Cont.d_pay_iDeposit_act is not null
Job.s_link_accts <> ‘N/A’
Job.l_wfl_stgMajor_id <> -5 (Maintenance)
Job.l_wfl_stgMinor_id <> -18 (Maintenance)
We search for a match in Dynamics FramProjVariation table where
FramProjVariation.docPkid = doc.l_doc_id
FramProjVariation.sourceDatabase = value for Framework Db Identifier
Known issues that prevent a variation from being inserted are
The date field must be between 1/1/1753 and 31/12/9999
Integrate other LookUp tables
Framework Prd_sType updates fwLuPrdSType
Framework Prd_hElev updates fwLUPrdHElev
Framework Prd_hFacade updates fwLuPrdHFacade
Framework Prd_hSeries updates fwLuPrdHSeries
Framework Prd_hType updates fwLuPrdHType
Framework prd_prom updates fwLuPrdProm
Framework rDiv updates fwLuRDiv
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.
...