I am creating SQL query inking multiple tables.
I would like to enable Filter on this query based on the different database lookup.
Concretely I want to have lookup of active company names (companies with invoices in last year), and use this as a filter for main query (all invoice details for a year sql query with joins).
when I try to trace this in SQL profiler i can see that metabase is sending out CompanyID as a text field with company name, how can I get ID but still have company names listed in the parameter fileds that user can choose from?
This is from SQL Server profiler after I try to refresh from metabase:
SELECT funGenericCompanyDaysCalc_1.*
FROM dbo.funGenericCompanyDaysCalc(@P0,"dbo"."Company"."CompanyName1" IN (@P1)) AS funGenericCompanyDaysCalc_1
Order By SeamanName ',N'@P0 date,@P1 nvarchar(4000)','2020-08-13',N'ATLAS d.d.'
If I change FieldFilter to ID field in data-table it is working but for users it is listing CompanyID's not Company names to choose from
Also, for FieldFilters is there possibility to use complex queries as a base, for example I would need to use something like this for CompanyID in above case (this is producing key value pair table) so could be used as a generic solution:
SELECT InvoiceFeeAssignments.CompanyID AS Value, Company.CompanyName1 AS Text FROM InvoiceFeeAssignments INNER JOIN Company ON InvoiceFeeAssignments.CompanyID = Company.CompanyID GROUP BY InvoiceFeeAssignments.CompanyID, Company.CompanyName1
@dinon Uhh, yeah, you cannot do that - Field Filters inserts SQL, which doesn’t play with the function call you’re doing.
You would have to do something like:
SELECT funGenericCompanyDaysCalc_1.*
FROM dbo.funGenericCompanyDaysCalc( {{rpt_period}}, (SELECT CompanyID FROM dbo.Company WHERE {{company_id}}) ) AS funGenericCompanyDaysCalc_1
Order By SeamanName
And if rpt_period is a range/period, then you would either need a lookup table and do a sub-select with min/max, or have two filter, a start and end date.
Ok,
many thanks now I understand how to do create FieldFilters, but If I want to use something specific I would need to create a separate query in database and use it as a base for FieldFilter and do it as a subquery within larger query.
Is there option to suggest his key/value pair strategy as enhancement?