The data from Excel will be placed in a value list and a sublist.
As an example, I have created an Excel file with the planets of our solar system and their moons.
The goal is to create a value list with all the planets, and a sublist with the corresponding moons.
In M-Files, this will result in a menu where you can choose a planet, and another (sub) menu that will show you the moons that belong to the chosen planet.
Prepare your Excel data:
In one sheet, make a list of all planets and give each planet a unique, numbered ID.
Select the ID's and planets.
Go to 'Formulas' -> 'Defined Names' -> 'Define Name' and name the collection: 'planet'.
Yes: Pluto is a planet ;)
In another sheet, make a list of the moons, give each moon a unique, numbered ID and also the ID number of the planet it's orbiting.
(This list of moons is not complete: I just used a few moons in order to populate this example list)
Select the ID's and planets.
Go to 'Formulas' -> 'Defined Names' -> 'Define Name' and name the collection: 'moon'.
Save and close Excel.
In Windows, make an ODBC connection to the Excel file:
With 'Select Workbook' you can search for your planets and moons excel file.
In M-Files, create a new value list, name it 'Planeet' and make a sublist called 'Maan':
In the 'Planeet' value list, make a 'Connection to External Database'. See your M-Files manual for instructions. Do not forget to enter 'Excel 8.0;' into the 'Extended Properties'
In the 'SELECT Statement' type: 'SELECT * FROM planet and hit the 'Refresh Columns' button.
Now choose your 'Target Property': The Object ID is column 'ID-P' from your Excel file. and the 'Name' is column 'Planeet' from your Excel file.
When you check the content of your 'Planeet' value list, you see:
The planets are in!
In the 'Maan' value list, you also make a External Database Connection.
You 'SELECT * FROM moon', 'Refresh Columns' and the target properties are:
'Owner' is the ID_P column from your Excel file;
'Object ID' is the ID_M column from your Excel file, and
'Name' is the Maan column from your Excel file.
When you check the content of your 'Moon' value list, you see:
And finally, when you take a peek a the content of your 'Planeet' value list:
Voila
I hope that other M-Files users find this helpful and I'm looking forward to your feedback.
