Workflow Help - Asset ID Identifiers Differing Between Two Tax Systems

[n_snapp92]

Hello,

I have two files attached, one file is an end result import file from my workflow that has everything correct except the Asset ID. The issue is the system we are importing into has Assets IDs in which the first 8 characters will replicate what is in our new system, however in some situations these IDs will be step up assets meaning they could be the same ID but then have a ".1" or ".2" attached (making the ID 10 characters). In some situations there will be just one layer, so just a 1 for 1 but the new system needs the ID to read with the .1 at the end, but in other situations there will be this parent and then child layers too in the new system, a .1 .2 .3 and potentially so forth. There will only be the one asset in the old system but the new system could have one or several with these layered pieces attached.

I need someone to help me create a hopefully not over complicated workflow that I could use and link in with my current that essentially compare the import file to the data dump from the new system and say okay this is a situation where a record has an ID that needs to be layered, maybe it needs multiple layers in which we would have to add records to the import file and then at the end of the day add in any new records with all the same data except the needing updated Asset ID. Remember there could be situations in which based on the new system data, the import file already has the correct ID, just needs the one record updated to have a ".1" or needs the ".1" and then additional records with a ".2" and so on in the asset ID.

Another tricky part is that you will need to concatenate the company code and asset ID on both sides as there are situations where different company codes have the same asset IDs so concatenating makes it completely unique.

Thank you!

Nick

[danilang]

Hi @n_snapp92 

You can use something like this

The formula tool on the bottom creates an [IDForMatch] field containing only the 1st eight characters of the Asset ID.  This is joined to old file on [Co Code] and [IDForMatch].  The join outputs one record for each of the matches with sub ids.  The Join tool also removes the AssetID from the old file and substitutes the AssetID from the new file with any potential sub ids.   The union adds all the matched records to all the unmatched ones 

This sample shows the results for old id 90026696 which has 3 sub ids under the new scheme.

Dan

[n_snapp92]

Thank you for this @danilang! One more piece that I missed. That field value on import, in any situation where the original Asset ID was found to have a layer, (or any characters past the first 8 characters) the new field value will need that same layer added on to it. What would be the easiest way to go about this? For example the ID was found to have a "C.1" at the end. I would need that added to the new field value and so forth.

Thanks again!

Nick