Insert Records with single lookup columns¶
RECORDS WITH VALUELIST ITEMS - SINGLE LOOKUP when a record is inserted from a external source it may be to add new valuelist items before the record can be added
-- the external source staging table should have an additional column for the MFID of each lookup column.
-- STEP 1 - determine if the new lookup value already exsit, add MFID in staging table MFID column, count number of missing items
-- use spMFCreateValueListLookupView to create new lookup view
-- example of code for single lookup with no owner
UPDATE Source
SET Source.MFCountry_ID = MF.MFID_ValueListItems
FROM Custom.stageMFCustomer Source
INNER JOIN dbo.vwMFCountry MF ON MF.Name_ValueListItems = Source.Country
AND MF.Deleted = 0
WHERE Source.MFCountry_ID IS NULL
AND Source.Country IS NOT NULL
DECLARE @Missing_Country_ID INT
SELECT @Missing_Country_ID = COUNT(DISTINCT Source.Country)
FROM Custom.stageMFCustomer Source
WHERE Source.MFCountry_ID IS NULL
AND Source.Country IS NOT NULL
-- example of single lookup with owner - state/province by country
UPDATE Source
SET Source.MFState_Province_ID = MF.MFID_ValueListItems
FROM Custom.stageMFCustomer Source
INNER JOIN dbo.MFvwValueList_StateProvince MF ON MF.Name_ValueListItems = Source.BILL_STATE
AND MF.OwnerName_ValueListItems = Source.BILL_COUNTRY
AND MF.Deleted = 0
WHERE Source.MFState_Province_ID IS NULL
AND Source.BILL_STATE IS NOT NULL
DECLARE @Missing_State_Province_ID INT
SELECT @Missing_State_Province_ID = COUNT(DISTINCT
ISNULL(Source.BILL_COUNTRY, '') + ISNULL(Source.BILL_STATE, '')
)
FROM Custom.stageMFCustomer Source
WHERE Source.MFState_Province_ID IS NULL
AND Source.BILL_STATE IS NOT NULL
-- STEP 2 - if step 1 show that it does not exist, then refresh valuelist items if users are allowed to add items in M-Files. Test again to confirm that the item was not already created by the user since the last sync.
-- Process Missing Value List Items
IF ( @Missing_Country_ID > 0
OR @Missing_State_Province_ID > 0
)
BEGIN
-- Refresh from M-Files
-- and update value list to see if anything still missing
-- add missing values to M-Files
EXEC dbo.spMFSynchronizeSpecificMetadata @Metadata = 'ValueListItems'
, @Debug = @debug
, @IsUpdate = 0 --M-Files to MFSQL
-- STEP 3 If not exists - then add a new valuelist item.
IF @Missing_Country_ID > 0
BEGIN
UPDATE Source
SET Source.MFCountry_ID = MF.MFID_ValueListItems
FROM Custom.stageMFCustomer_FutNew Source
INNER JOIN dbo.MFvwValueList_Country MF ON MF.Name_ValueListItems = Source.BILL_COUNTRY
AND MF.Deleted = 0
WHERE Source.MFCountry_ID IS NULL
AND Source.BILL_COUNTRY IS NOT NULL
SELECT @Missing_Country_ID = COUNT(DISTINCT Source.BILL_COUNTRY)
FROM Custom.stageMFCustomer_FutNew Source
WHERE Source.MFCountry_ID IS NULL
AND Source.BILL_COUNTRY IS NOT NULL
IF @Missing_Country_ID > 0
BEGIN
-- add missing value list items
INSERT dbo.MFValueListItems ( Name
, MFValueListID
, OwnerID
, Process_ID
)
SELECT DISTINCT BILL_COUNTRY
, MFVL.ID
, 0
, 1
FROM Custom.stageMFCustomer_FutNew
CROSS JOIN ( SELECT ID
, Name
, MFID
FROM dbo.MFValueList
WHERE Name = 'Country'
) MFVL
WHERE MFCountry_ID IS NULL
AND BILL_COUNTRY IS NOT NULL
END --IF @Missing_Country_ID > 0
END --IF @Missing_Country_ID > 0
-- STEP 4 perform step 1 - 3 for all lookup columns to create all the new valuelist items, then process update.
-- STEP 5 get new valuelist item id's to use for inserting new record.
-- Write back to M-Files any missing values
-- update staging with newly created IDs
IF EXISTS ( SELECT 1
FROM dbo.MFValueListItems
WHERE Process_ID <> 0
AND Deleted = 0
)
BEGIN
-- At least one value list item needs to be updated
EXEC dbo.spMFSynchronizeValueListItemsToMFiles @debug
IF @Missing_Country_ID > 0
BEGIN
UPDATE Source
SET Source.MFCountry_ID = MF.MFID_ValueListItems
FROM Custom.stageMFCustomer_FutNew Source
INNER JOIN dbo.MFvwValueList_Country MF ON MF.Name_ValueListItems = Source.BILL_COUNTRY
AND MF.Deleted = 0
WHERE Source.MFCountry_ID IS NULL
AND Source.BILL_COUNTRY IS NOT NULL
END --IF @Missing_Country_ID > 0
IF @Missing_State_Province_ID > 0
BEGIN
UPDATE Source
SET Source.MFState_Province_ID = MF.MFID_ValueListItems
FROM Custom.stageMFCustomer_FutNew Source
INNER JOIN dbo.MFvwValueList_StateProvince MF ON MF.Name_ValueListItems = Source.BILL_STATE
AND MF.OwnerName_ValueListItems = Source.BILL_COUNTRY
AND MF.Deleted = 0
WHERE Source.MFState_Province_ID IS NULL
AND Source.BILL_STATE IS NOT NULL
END --IF @Missing_State_Province_ID > 0
END --IF EXISTS ( SELECT 1 FROM dbo.MFValueListItems WHERE Process_ID <> 0 AND Deleted = 0
END --IF ( @Missing_Country_ID > 0 OR @Missing_State_Province_ID > 0 )