Custom Connector Dynamic Parameters

[Beginna]

Hello,

I am developing a custom connector.
Is there a way to dynamically generate the parameters that a user selects?
Specifically, I want to identify which table is selected in the navigator and query database parameters on this basis. However, the number and names of the database parameters are dependent on the table name and can be queried using a native query.

Thank you very much!

[Beginna]

@Greg_Deckler does this Help you? Basically the number of the Parameters of a function should be dependent of the selected view in the navigator

[Greg_Deckler]

@Beginna Right, so when populating the "Data" portion of your navigation table, you should know the table name so you could create a function for getting the data that passed the table name into the function as a parameter and then based on that table name you would know how many parameters to use when retrieving the data. It seems somewhat similar to this from The Definitive Guide to Power Query (M):

Note that I get the guilds (tables in your case) and then for the data for that guild/table I then call a function with, in this case the id of the guild (table name in your case). That function then uses the information build the correct API string to execute. In your case, you would know how many parameters to use.

TDGTPQM_Discord.MemberNavigation = () as table =>
    // Navigation
    //** Membership Navigation
    let
        servers = Table.ExpandRecordColumn(TDGTPQM_Discord.GetGuilds(), "Column1", {"name", "id"}, {"Name", "Key"}),
        addDataColumn = Table.AddColumn(servers, "Data", each TDGTPQM_Discord.GetGuildMember([Key])),
        addItemKindColumn = Table.AddColumn(addDataColumn, "ItemKind", each "Record"),
        addItemNameColumn = Table.AddColumn(addItemKindColumn, "ItemName", each "Function"),
        addIsLeafColumn = Table.AddColumn(addItemNameColumn, "IsLeaf", each true),

        Navigation = Table.ToNavigationTable(addIsLeafColumn, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")       
 
    in
        Navigation;


// ** Member User
TDGTPQM_Discord.GetGuildMember = (guildid as text) as table =>
    let
        apiCall = Json.Document(
            Web.Contents(
                api_uri,
                [
                    RelativePath = "users/@me/guilds/" & guildid & "/member"
                ]
            )
        ),
        output = Table.FromRecords({apiCall})
    in
        output;

[Beginna]

@Greg_Deckler 

Hello,

thanks for the answer.
But unfortunately I still don't understand the last step.
If I now have the number, types etc. of the database parameters, how can I have them entered by the user? Especially if the number of parameters per database table is different?
Sorry, I am completely new to this topic.

[Greg_Deckler]

@Beginna I'll have to test it out but I don't believe you can surface prompting from the user in that circumstance. I've only gotten that to work when initially firing up a connector, when you initially connect that is. 

[Greg_Deckler]

@Beginna So this sounds possible but not sure I have enough information to provide specific help. Can you provide an example of a native query that gets these parameters? And then once you have the parameters, how do you use them?

[Beginna]

@Greg_Deckler 

I get the parameters for the TEST_VIEW view in the TEST schema via the native query:
Value.NativeQuery(Source, ‘SELECT SCHEMA_NAME, OBJECT_NAME, PARAMETER_NAME, IS_MANDATORY, DEFAULT_VALUE FROM SYS.CS_VIEW_PARAMETERS WHERE SCHEMA_NAME = “TEST” AND OBJECT_NAME = “TEST_VIEW”;’)

If the parameter IP_2 has the value 2, you can use this when querying the TEST_VIEW view:
b = Value.NativeQuery(Source, ‘SELECT * FROM TEST.TEST_VIEW (placeholder.$$IP_2$$=>2);’)