Enhance CDS Excel add-in

[Yahya]

Modeling my CDS database, I am also developing my PowerApps BUT Excel add in has been of a GREAT benefit meanwhile, for importing data (I prefer Publish  data over the Import for many reason those I can post about separately). 

I even think that for some reasons, including delegation limits (!), I may find myself preferring working on Excel rather than PowerApps! Of course after adding some business rules, security rules, Flows etc, with these, Excel can be a very nice way to manipulate your nice CDS data!

However, Excel Add in for CDS needs lot of improvements, here I list some: -

1. If the excel book table (entity) is filtered and saved, closed, then reopened, you will get an unnecessary error, that you need to clear filter before you can Refresh, Edit and Publish Data. When your excel book has few or lot of tables, it is a hassle to find which table has the filter to clear 🙂 Please add to CDS the power to clear filters upon opening the excel book with CDS add in. Simple.
2. In look up columns, you get the data from other entities in the right pan, ALL data, organized in columns order that I never understand. It works but it is headache! Please enhance Excel add in to be reading lookup fields from VIEWS those I can create, organize columns and even make filters outside excel (at view level). For Instance, to choose a Default contact for an account, Why i have to see all contacts even they are not related to the selected account? wrong.
3. some default conditional formatting or at least column headers would be nice to reflect column features such as required, etc.
4. Direct the pointer to the first cell of the row created after you hit New
5. For Business Rules, create a message to make a hint (created by the developer) for the user, that message then should be available in PowerApps and Excel!
6. Make CDS a datasource in Excel PowerQuery, this is a separate IDea that is under review, i swear it is a must 
7. find a way to represent the Look up column by a single column rather than 2. 

[Yahya]

8. Make it possible to prevent users go to the design of the CDS Add in in Excel.

[Yahya]

@CWesener

[CWesener]

Thanks @Yahya, will review.

[Yahya]

Thanks @CWesener, here is point 9 which is very interesting: -

In CDS excel add in, when you hit a lookup column, the add in shows the data from the Lookup entity, usually pressing all fields which makes it hard to read. I suggest that this CDS addin part is also handled as a view where in I can sort, filter records while selecting which columns to show from Lookup entity.

[Yahya]

One more thing, dear @CWesener

When you have an excel workbook with CDS imported via CDS add in, and the case that you for instance delete a field from the entity (from portal) while it was already mapped (shown) in the excel workbook, when you try open the workbook you get an error that then there is no way to use the workbook again. error says

Error Loading Metadata Details: Please check server connection, verify the current user has proper access, and try again. Error message: 'Metadata was unable to be retrieved for entity ENTITYNAME as it was not found.'

same goes when you delete an entity and open an excel workbook where in that entity is mapped.

this needs to be improved simpy by enabling the Design button, which is disabled in case of error.