Exporting Data from Excel to M-Files

This topic has turned up in a support case recently, so I'd like to be clear here: you can import data that includes multiple foreign-key relationships.

What you cannot do is import them from columns holding delimited strings. Instead you must ensure that multiple rows are returned, one for each foreign-key relationship. This is similar to doing a SQL "outer join". Let's take the scenario of a Customer and a Project.

Here is our customer table:

Customer ID Name
1 CustOne
2 CustTwo

Here is our project table:

Project ID Name Customer ID
1 ABC 1
2 DEF 2
3 GHI 1
3 GHI 2

As we can see from the data above, there is one project that is just for customer 1 (ID = 1, Name = ABC), and one project that is just for customer 2 (ID 2, Name = DEF). However, there is also one project that is for both customers 1 and 2 (ID = 3, Name = GHI).

If the data is structured this way then you can map the project's "Object ID" property to the "Project ID" column (yes, there are two rows with the same unique ID, but M-Files will deal with it), and the project's "Customer" property to the "Customer ID" column. This will result in a single Project in the system with ID 3, but two entries in the "Customer" multi-select lookup.

I hope that helps.

Regards,

Craig.

This topic has turned up in a support case recently, so I'd like to be clear here: you can import data that includes multiple foreign-key relationships.

What you cannot do is import them from columns holding delimited strings. Instead you must ensure that multiple rows are returned, one for each foreign-key relationship. This is similar to doing a SQL "outer join". Let's take the scenario of a Customer and a Project.

Here is our customer table:

Customer ID Name
1 CustOne
2 CustTwo

Here is our project table:

Project ID Name Customer ID
1 ABC 1
2 DEF 2
3 GHI 1
3 GHI 2

As we can see from the data above, there is one project that is just for customer 1 (ID = 1, Name = ABC), and one project that is just for customer 2 (ID 2, Name = DEF). However, there is also one project that is for both customers 1 and 2 (ID = 3, Name = GHI).

If the data is structured this way then you can map the project's "Object ID" property to the "Project ID" column (yes, there are two rows with the same unique ID, but M-Files will deal with it), and the project's "Customer" property to the "Customer ID" column. This will result in a single Project in the system with ID 3, but two entries in the "Customer" multi-select lookup.

I hope that helps.

Regards,

Craig.