JSON Connector that allows for entire dynamically constructed url?

ccccc4
ccccc4 Member
in Connectors

I am trying to use the JSON No Code connector to dynamically pull data from another website. I've used this for other purposes successfully, but am running into an issue with the way the Domo connector inserts a "/" or "?=" in the url under the dynamic url and dynamic parameters settings. If I use the static url option, it works correctly but I'm unable to piece together the url using the dynamic settings.

If I run this single url, it works: https://website.com/financemodel/data/extract/gldata?Scenario=Actual&FiscalYear=2024&ApplyCreditDebit=True&$filter=FiscalMonth eq 12

If I use the dynamic settings where the url is this: https://website.com/financemodel/data/extract/gldata?

and then insert the dynamic values or even insert the end of the url using a Dataflow, it doesn't work due to the url being invalid. I suspect it's because the FiscalMonth can only be filtered using OData "$filter=FiscalMonth eq" instead of just "?FiscalMonth=". I've tried different combinations of url and dynamic pieces of the url to tack on but can't get it to work.

Is there a way to have a JSON connector pull the entire url from a dataset? I can easily build the full url in ETL instead of trying to splice it together using the JSON url settings.

The whole reason for this is that the source data does not contain a date field to use as a parameter. There are only fields for the Year and Month

Screenshot 2024-10-24 111242.png

Tagged:

Answers

  • ggenovese
    ggenovese Contributor

    Can you try making your parameter name "ApplyCreditDebit" and your value "True&$filter=FiscalMonth eq 12" and see if that works? Also, you may need to turn off encoded for your values. If you click the blue Encoding link and uncheck values that will prevent the connector from trying to encode your manual input

  • ArborRose
    ArborRose Coach

    You can't directly reference a dataset field in the textbox for "Single URL," so you probably need to approach it a bit differently using Dynamic URL with a dataset.

    In your ETL or DataFlow, concatenate the full URL (including all parameters like Scenario, FiscalYear, $filter=FiscalMonth, etc.) into a single column. For example, the dataset would have a column like:

    https://website.com/financemodel/data/extract/gldata?Scenario=Actual&FiscalYear=2024&ApplyCreditDebit=True&$filter=FiscalMonth eq 12

    Configure the JSON Connector - instead of using Single URL, select Dynamic URL. In the Base URL field, leave the base part of the URL (e.g., https://website.com/financemodel/data/extract/gldata?).

    In the Dynamic Parameters section, use a single dynamic field that references the dataset column with the complete URL string (starting with Scenario=...). This way, it will pull the entire query string part dynamically from your dataset.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ccccc4
    ccccc4 Member

    No matter how I divide up the URL into the base part and the dynamic part from the dataset, it seems to insert an additional "/" into the resulting url. Even if I structure it so the slash fall where one of the slashes in the url is, it returns an invalid url

  • ggenovese
    ggenovese Contributor

    I think what @ArborRose mentioned, and what I had missed, is that you're using the Dynamic URL option which adds the slash. Try using the Dynamic Parameter option instead.

  • ArborRose
    ArborRose Coach

    In the base URL, do not include a slash and avoid adding a question mark if you plan to include the entire query string dynamically. When you create the dynamic parameters, do not include an initial question mark nor a slash. Format the dynamic parameters so they start directly after the base URL, something like:

    Scenario=Actual&FiscalYear=2024&ApplyCreditDebit=True&$filter=FiscalMonth eq 12

    You can diagnose and test using a simpler URL at first to see if it constructs correctly.

    Base URL: https://example.com/api
    Dynamic Parameters: param1=value1

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ccccc4
    ccccc4 Member

    No matter which combination of base url and dynamic parameters I use, it doesn't work. I can't seem to get around the "?=" it inserts in between the base url and parameter. This might just be a limitation of the Domo connector.

    Appreciate your help!

    Screenshot 2024-10-25 080744.png

  • ggenovese
    ggenovese Contributor

    If you don't need to loop through dynamic parameters then this should work, note that I have unchecked encoding on the values

    image.png
    image.png

Categories