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	Create	Update	Framework Database Field Name
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	Create	Update	Framework Database Field Name
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	Create	Update	Framework Database Field Name
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	Create	Update	Framework Database Field Name
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	Create	Update	Framework Database Field Name
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	Create	Update	Framework Database Field Name
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	Create	Update	Framework Database Field Name
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	Create	Update	Framework Database Field Name
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	Create	Update	Framework Database Field Name
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	Create	Update	Framework Database Field Name
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.

Get Default Framework Database Identifier from Preference #-1024
Integrate Jobs

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’
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
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.

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
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
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.

Browser not supported