Hi there,

We have configured our import to tick the Deceased Check box in Bio1 when the TM company returns a value of Yes in the Deceased Flag field.

This works fine for the check box but we want to also ensure that the Deceased Date field in Bio1 has the current date added to it. Upon import, these records have this field as blank at the moment. 

I have also tried adding a virtual field to update the Deceased Date with the current date based on a seed of the Deceased flag field but it still adds the Deceased Date and hence the flag to every record in the import.

Wondering if there might be a way to add the Deceased Date virtually perhaps with a dictionary and a RegEx that uses the Yes to populate with today's date?

Grateful for your help.

Cheers,

Tessa

Tessa, personally I would not use RegEx for this - I would insert one line of code in your 'afterdictionary' section - something like;

If import.Fields.GetbyExcelName("[whatever your deceased flag column is").Value="Yes" then

  import.Fields.GetbyExcelName("whatever your deceased date column is").Value=Date()

End if

Note: none of the above is tested or syntactically checked

Thanks, Dennis dennis@ladnierfamily.com

Thanks Dennis,

I have added the below code into the API but get the following errors:

If Import.Fields.GetByExcelName("CD").Value = "Yes" Then Import.Fields.GetByExcelName("CM").Value = Date()

  End If

Tessa, as noted, the code was not tested or syntactically checked - however the below was so should work.

   If Import.Fields.GetByExcelName("CD").Value = "Yes" Then

         Import.Fields.GetByExcelName("CM").Value = DateTime.Now.ToString("dd/MM/yyyy")

  End If

Thanks, Dennis dennis@ladnierfamily.com

Hi Dennis,

I am back after a year of maternity leave and thought I'd give this another crack. When I test this code with the 'Test Code' function in IOM I get a funny value - 

 If import.fields.getbyexcelname("CD").Value="Yes" Then

  import.fields.getbyexcelname("CH").Value=Datetime.Now.ToString("dd/mm/yyyy")

  End If

When I import it for real I get the following error: 

Source row 1 produced Excel error row 2: Invalid Fuzzy Date: Deceased Date [Object: 'CRecord', PK: '210478', Import ID: '00001-593-0000210478', Desc: 'Chanelle Afford', Field: 'Deceased Date', Value: '30/19/2021'] -->BBREAPI.CRecordClass.Save()-->ImportOM.CImport.#=qnHhi0s$5I4AHingPxwMbxAp7ci2VC8kmAWLjV7pfF$w=()

Hi Tess

The odd value for months is probably because you've typed a lower case "mm", which is minutes. Try "dd/MM/yyyy" as Dennis correctly suggested.

Cheers

Nick

Oops - my typing sucks too. I meant "Hi Tessa". Sorry.

Thanks Nick and Dennis! 

I have now got this working. I'm such a newbie not knowing which is months and which is minutes :)

Thanks again!

Tessa