relation between different database

[Jacques]

Hi,
I have seen that it's possible to have a list or a form which is related to reports belonging to other database.
Is it possible to have on the same line of a list different columns related to different database ?

I explain.
In a normal application the users are using some code in some fields. They know their signification.
The external user doesn't know those codes.
I imagined to put the codes and the description of the code in an excel file, to make a relation between the code of the real database and the code of the excel file and to put on the list the code with its description (or only the description) between the others columns belonging to the real database.

Jacques

[Carsten Sørensen]

Hi Jacques,
Yes you can lookup values on detail listings by making the column SELECT columns (as you do for listedit) and base the SELECT list on another list resolving the values or even show missing / invalid codes.
Best regards
Carsten

[Jacques]

Carsten,
Do you speak about the SQL in the "extra column and key figure calculations" in the detail of the list ?
How can I take a field of another report (or list) related to the main list ? Could you give me an exemple ?
It's perhaps a question of expression. (select [[ ???? ]] from [[ ???]] ?)

Thanks
Jacques

[admin]

Hi Jacques,

I'll show you a very brief example of what I think you'd like to do. I chose this example because the necessary data are in the demo installation.

In the excel sheet "Sample_95.xls" there is a range called countryCodes with 3 values:


In the access demo V6_demo.mdb, we have a table, Employees, that has a field with country codes:


I want to use the Excel list as a filter for the employees list.

First I must save the excel list as a report and remember the name. Mine is XLCountries. I assume it is not necessary to show this.

Next, on our Employees report, we add CNTRY as a filter:



Next to "hook up" the excel filter as a pick list
Go to the List Options tab, and locate the Country filter on top
You need to specify "Select" as Search Type. Once you do this, you will be able to set further options:

Locate the Excel list you made above (XMCountries) and select it as the source:
Select the Code as the Value field and the Country as the Display field.

Save and go:

Notice, how the filter drop down uses the Display column (Country Name) while the list is actually filtered on the Country Code.

NOTE:
I showed one way of accomplishing this. The recommended way if you need to do this often, is to use the Dictionary to define this. In the dictionary, you can point to a reportID with Value and Display Name to be used as a suggested list when creating Drop down filters. This way, you would need to only define the drop down filter once, and use the same definition for all reports on the Employees table.

Best regards
Bo

[Jacques]

Thank you Bo !
It was very clear and I have succeeded to do it with my data's !

Another point of my question was if it was possible, in your exemple to have the word "Germany" instead of "GE" in the column of the list, and perhaps the other country corresponding on the other code in the general liste.

The country code is a good exemple. Not a lot of people will know that IL = Israel or XS = Serbia or XM = Montenegro, ... It would be good to find those texte instead of the code in the list.

If the country table were on the same database as the other data's, I can make a dynamic SQL view with a join between the both tables and use it but not if they are in 2 different databases (Excel and SQL server for exemple) I can't.

Jacques

[Bob Cergol]

Hi Jacques,

Since the selection list is just another report -- you can create an extra column in the report with a case statement like: case when country = 'GE' then 'Germany when country = 'DE' then 'Denmark' else 'unknown' end

Then make the report use the code column and the extra column. Then the select list in your filter will show Germany, Denmark, etc. but feed the report 'GE', 'DE' ,etc. as the actual filter value.

I'd do a screen shot for you but I'm travelling and in between meetings at this moment.

The report that you assign to the filter does not have to be from the same database. It just needs to have a column containing the common country code values.

Regards,
Bob

[Jacques]

Thanks Bob,

I have done it. It's working very well.
In "Formula / SQL" field of extra columns and key-figure calculations, what can be used ?
I have seen that we can use SQL functions (like convert, ...) or calculation between columns.

I think it's not possible to use a more complicated sql statement looking for a value in another table of the database. For exemple looking for the name of the country in another table of the same database. To do that, it's beter to do a Dynamic SQL view.

Am I right ?
(For the moment, I am studing all possibilities of DAI)

Jacques

[Bob Cergol]

Hi Jacques,

You are correct. You can only use expressions in the plus columns. You can't use select statements. I think the limit is not Dynamic AI so much as SQL syntax itself. All of this stuff ends up in a sub-select by the time it gets sent to the backend so some statements in a plus column wouldn't make sense.

I also think you are right that at some point you find it advantageous to use a Dynamic view instead of a Dynamic join with plus columns. There is so much you can do in a view to further, dynamically "Extract and Transform" the data for Dynamic AI to "Load".

Don't overlook the ability to create "dictionaries" for both your Dynamic views and joins. This is basically creating plus columns at the data source level instead of the report level so they become globally available to all reports. It's true, you could create a formula column in the Dynamic view itself -- but as a plus column it is applied only to the result set returned by the query. You can also define enforced filters and roles in your dictionary that constrain the results of row & column information revealed in any given report based on the profile of the user who is running that report. That is very powerful.

There's a lot to like about Dynamic AI and a lot to explore. That made it appealing to me -- quick, impressive results -- but plenty to chew on and keep progressing with its capabilities.

Best regards,
Bob