Best Of

Quick summary of the less intuittive aspects well-explained by Dan:

Data types are configurable in the SQL Query per column

This is how you assert for a "person" (see user id)

The current row object then becomes it's own module so when you select take action see below this then accepts the currentrow.variable rather than result.variable

You can reference different versions of the same workflow, which allows for breaking the workflow down into multiple parts particularly useful for User actions that may require a user's response:

CAVEAT: This means, after deployment, if you need to edit a sequenced step you'll have to create and deploy a new version of every step prior and re-link them within the StartWork function which is a pain (correct me if I'm wrong @DanHendriksen)

@DanHendriksen I've not done this justice so feel free to add anything but also this would be a huge gap-filler as a walk-through on the workflows playlist (Youtube). Thanks for the help!

Hey Domo Fam! Wanted to share a success story using Magic ETL + Python scripting to solve a problem. My first use of a python tile!

Challenge

I'm building a Company Dimension table using Hubspot company data...all connector reports have technical column names (over 450 columns) and all date fields (50+ columns) are a mix of unixtimestamps, some with milliseconds, some without, some already as dates and yet others as timestamps. There are too many columns for a MySQL flow input (it has a limit), and managing hundreds of columns in a formula tile or select columns is unmanageable and too manual.

The objective is to rename all connector report columns to the proper UI label and convert all date or time related fields that are UNIXTIMESTAMPs over to a DATE type.

Here is the source Hubspot All Companies Dataset Preview from the connector for a few columns:

My first step was to find the Hubspot API endpoint that provided the mapping of the technical name to the UI label, which exists at this endpoint - https://api.hubapi.com/properties/v2/companies/properties.

I setup a basic JSON No Code Connector parse that data and it's looking good and provides the mapping, which doesn't always match up exactly by simply converting to Title Case or removing underscores, for example: hs_is_enriched on backend displays in Hubspot property as "Has been enriched"

Python Scripting Tile

Magic ETL was a great solution and python allowed me to make this mapping and the date conversions in bulk for hundreds of columns

Here is the first tile handling the column rename - a few lines of code with a mapping dictionary to rename 450 columns

from domomagic import *

# Read data from inputs into data frames
input1 = read_dataframe('Hubspot | All - Companies')
input2 = read_dataframe('Hubspot API | Company Properties')

# Create name to label mapping dictionary
mapping_dict = dict(zip(input2['name'], input2['label']))

# Rename columns where they exist in the mapping
existing_cols = [col for col in input1.columns if col in mapping_dict]
rename_dict = {col: mapping_dict[col] for col in existing_cols}

# Apply the renaming
result = input1.rename(columns=rename_dict)

# Write the transformed dataframe to output
write_dataframe(result)

And here is the second handling the date conversions. A bit more complex given the variety of inputs and column names, but nothing a little AI couldn't help me figure out.

from domomagic import *
import pandas as pd

# Read the input dataframe
df = read_dataframe('Python Script - Bulk Column Rename')

# Find date columns
timestamp_cols = [col for col in df.columns if 'date' in col.lower() or 'time' in col.lower()]

print("Processing timestamp columns:", timestamp_cols)

for col in timestamp_cols:
    try:
        # Skip empty or all-zero columns
        if df[col].isna().all() or (df[col] == 0).all():
            continue
            
        # If already in datetime string format
        if pd.api.types.is_string_dtype(df[col]):
            df[col] = pd.to_datetime(df[col], errors='coerce')
            continue
            
        # For numeric timestamps
        if pd.api.types.is_numeric_dtype(df[col]):
            # Get non-zero max value to determine timestamp type
            max_val = df[col][df[col] != 0].max()
            
            # 10-digit timestamp (seconds)
            if max_val < 10000000000:
                df[col] = pd.to_datetime(df[col], unit='s')
            # 13-digit timestamp (milliseconds)
            elif max_val < 10000000000000:
                df[col] = pd.to_datetime(df[col], unit='ms')
            # Larger timestamps (microseconds)
            else:
                df[col] = pd.to_datetime(df[col] / 1000, unit='ms')
                
        print(f"Converted {col}. Sample values:", df[col].head())
        
    except Exception as e:
        print(f"Error converting column {col}: {str(e)}")
        continue

# Write the final result
write_dataframe(df)

John, an email user task cannot have a dynamic recipient at this time.

I would encourage you to use the form user task. The form can be very simple and only require a single response, but in my experience (which is vast) is a much better way to go for several reasons:

1 - You can get more than a single predefined response
2 - You can have the recipient be dynamic
3 - They can get push notifications in the Task Center App

I could go on, but in my experience everyone thinks they want the email user task until they've used it once. The user will get an email on their phone, click the button and it will launch Domo in the browser. They won't be logged in, so they'll have to go through the authentication flow, then their response is recorded and they can close the browser. Furthermore, if this is something with any frequency, they're having to go back through a crowded inbox to find the things that are waiting for them.

Task Center, both in the mobile app and Domo web app, provides a much more comprehensive and user friendly experience. If you need help with it, let me know.

Now, if you are hell bent using the email user task, there is some creativity that can accomplish it. You can dynamically add a user to a group, send the task to that group, and then after they complete it remove the user from the group. It can get messy and you could potentially have race conditions where multiple people are in the group, but it's a creative approach I saw someone use (before they realize the Form User Task just works better) that I figured might be worth mentioning.