Best Of
Re: Pivot Table Static First Column
You can skip the last step of using COALESCE and just leave the values of NULL in place. Then count won't include those and should give you 0 for those.
Re: Pivot Table Static First Column
You’ll need to have a record for each rep for each day. To do this you can import the Calendar.csv from the Domo Dimensions connector. Then run a select columns on your dataset to pull just the rep name. Run that through a distinct tile. Then for both of those use two separate Add Contant tiles to add a column called Join Column and set the value to 1. Then use a join to join both datasets based on the join column. You can filter your dates to ignore ones in the future and any dates prior to the earliest sales rep date. Take this joined column dataset and left join your original dataset to it you should now have records for every rep for every day. You can then use a formula tile to do a COALESCE function to default any nulls to 0
Re: Dynamics 365 Emails - From, To and CC
Hi @FXM32
You're receiving List as a text in your dataset because the CRM API is returning a list of values but the connector can't handle those values. You'd need to get CRM to return one record per CC or To field or you could write a custom connector to handle the data coming from the API properly. You may reach out to Support to see if they can change the connector to return the values in a CSV list instead of just the word List but I'm not certain if Domo specifically owns the connector or if it was written and published by someone else.
Re: Adding Missing dates between start and end date for each category
You are on the right track using the Domo Calendar. What you need to do is an Add Constant tile after each dataset and create a column such as JoinCol and assign a value of 1 to each. Then join the two datasets on that column, using a left join of the calendar so that you get all the dates. If you are just wanting the first day of each month from the calendar dataset, you can filter before the join to where day = 1.
Here's an example of what the ETL would look like.
Re: Domo API sample
That dataset ID doesn't exist in your instance so it's throwing an error. You can create a simple testing dataset using the WebForm connector and putting in some sample data if you want to test it before using real production data. You can get the new dataset ID from the URL when you're viewing the dataset details.
Re: Domo API sample
This is the webforms connector I was referring to:
https://domohelp.domo.com/hc/en-us/articles/360043436813-Webforms-Connector. Just search for WebForm in the connectors dialog box.
Re: Pivot table issues
Hi @user031170
There's a 25k row limit on most card types except for Histograms and Box Plots (100k limit). See https://domohelp.domo.com/hc/en-us/articles/360042924214-Card-Building-FAQs
Have you tried using a Sumo Table to do your pivot? Those have a higher row limitation and may allow you to chart your data properly.
Re: Is there a way to check if a field has more characters than a specific amount?
Hi @JoeR_PSIT
You can use the LENGTH function to get the number of characters in a string
CASE WHEN `Serial Number` IS NULL OR LENGTH(`Serial Number`) > 7 THEN 'Error' ELSE 'OK' END
Re: Api fails for filters
@sunny for your card is the public embed link correct? 404 means it can’t find anything at your URL. Are you able to take that URL and access it from a browser? Did you confirm the embed ID is the same as the Domo Everywhere configuration settings on the card?