Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Current »

This is how I did this, using my knowledge of Excel… it may not be the most efficient way to do it, but

You can export the remap settings for a source format as XML, and then convert that XML to a spreadsheet for review/testing.

  1. Use a custom SQL query to pull all columns of the source format table:

    select sf__JID_.[remap] as [Remap XML]
from [source.format] sf__JID_
where
(
	/* REPLACE GUID VALUE WITH GUID OF SOURCE FORMAT */
	(sf__JID_.[id] IN ('25a06a6a-5184-4a6b-b538-e81fee4a4a15'))
)

  2. Copy the resulting XML in full and paste into a plain text file and save.

  3. Open Excel and go to File > Open. Browse to select your XML file and when prompted choose “As an XML Table.” It will probably tell you you do not have a schema, so ask it to create one automatically. You should end up with something like this: image-20241204-141442.png

    1. src = the column header/field name from the source

    2. dst = the Slate field where data will be saved

    3. dst2 = the second Slate field where data will be saved (if mapped)

    4. src2 = the field value from the source that will be mapped to the dst/Slate field

    5. dst3 = the prompt GUID that will be used for the value in src2 and saved in the dst/Slate field

    6. dst24 = the prompt GUID that will be used for the value in src2 and saved in the dst2/Slate field (second field)

  4. Now you need to bring in the value associated with the prompt GUID. Run a quick query in Slate using the Configurable Joins base of System > Prompt. Export the prompt value and GUID for any prompt keys that you need to pull into the XML table. image-20241204-141908.png

  5. Return to the Excel file and open a new sheet; copy just the columns that have the values from the source and the prompt GUIDs and paste into the new sheet as values only. In a new column next to the Prompt GUID, use XLOOKUP to connect to the spreadsheet you produced in Step 4 (prompt values) and pull in the values.

    1. Lookup_value should be the top cell in the column of Prompt GUIDs

    2. Lookup_array should be the column in the prompt values spreadsheet that contains the GUIDs.

    3. Return_array should be the column in the prompt values spreadsheet that contaings the values.

    4. If_Not_Found should be NOT FOUND (just text).

    5. After setting up the first cell, copy & fill down the spreadsheet. The Lookup_Value reference will be updated for each cell.

  6. After you finish pulling in all values needed from the prompt values spreadsheet, highlight the entire table and paste as values to retain the information but disconnect the XLOOKUP formula. You can now delete the prompt values spreadsheet and save just this sheet as your prompt mapping.

  • No labels