cmty_blog_detail (2024)

@EricRegnier- I'm having the same issue as @Lukewarmin that I want to create a relationship between multiple tables in which I define the fields on which to join. I'm not trying to create a lookup but instead to build a filter for a lookup based on relational data. But I cannot relate the data. I have no way to tell Dataverse that I want to pick projects from a list and to limit that list based on the currently selected contact by using a table of data that matches the contact to their projects.

I'm finding Dataverse very inflexible and difficult to use. I'm working with multiple systems and multiple databases including Postgresql and SQL Server. How can I meaningfully integrate all of the data from all of the systems so users can access it in a Dataverse form? There are so many connectors for creating these things, yet once you have something in Dataverse it doesn't seem to have the ability to provide for meaningful relationships.

Hi@Lukewarm,

In Dataverse the method to join tables automatically is withlookupcolumns. Dataverse will automatically create a relationship and maintain the foreign key (guid). In model-driven apps, the table is automatically joined and with canvas app or Power FX, you can use intellisense to navigate to the joining the table. If you're trying to join within code, there are multiple methods with SQL, OData or Fetch XML:

• SQL:https://learn.microsoft.com/power-apps/developer/data-platform/dataverse-sql-query
• OData and $expand:https://learn.microsoft.com/power-apps/developer/data-platform/webapi/query-data-web-api#join-tables
• FetchXML:https://learn.microsoft.com/power-apps/developer/data-platform/use-fetchxml-construct-query#create-the-fetchxml-query-string

In C# you can also use QueryExpression or Linq:

• QueryExpression:https://learn.microsoft.com/power-apps/developer/data-platform/org-service/build-queries-with-queryexpression
• Linq:https://learn.microsoft.com/power-apps/developer/data-platform/org-service/use-linq-construct-query

Hope this helps....

@PaulD1I agree with a lot of your points about Dataverse and that there are limitations in doing certain types of queries. My background is in Dynamics, but I also worked with SQL. Your address example is not the best example, as it is actually set up more like you recommend behind the scenes. The address records are actually stored in the address table and just reflected in the address fields for convenience. So you can actually find accounts by address by searching one place.

If a customer has a SQL DBA available, then I would probably recommend that they use SQL. The problem is many (even large) companies do not, or if they do, they are busy supporting their important enterprise systems, and the average Power Apps/Power Automate person is not a SQL expert.

That is the biggest plus of dataverse--it gives a structured database that is approachable by people who are not DBA's and allows enterprise companies a standard way of controling access to key data in ways that can be managed by less technical people (dataverse security roles, etc).

I would encourage avoiding a "good/bad" outlook on data sources and connections--they are all useful when used correctly. There is a point for using SharePoint, SQL, and Dataverse. I think it is fair to say "if your data model is highly complex with numerous data joins and unions and you have people or partners available to support it, use SQL." Also high data volumes with busy transactions that would exceed the API limits in Dataverse. But there are other cases where Dataverse makes sense. Solutions, complex security scenarios requiring frequently changing security roles for user/teams, etc. And with advances in virtual tables it is less of an issue.

@kshepard, I beleive you have the right path and approach by trying Dataverse out and doing some prototypes/POCs. It is Microsoft's recommendation with it comes to internal business apps and where there's a lot of investment from their side.
SharePoint list can also be fine but becare as not meant for complex scenarios and harder to scale. See this post:https://crmtipoftheday.com/1402/why-you-should-not-use-microsoft-lists-sharepoint-lists-for-business-critical-power-apps/

@kshepardI'm not an expert so your mileage with my response may be limited...

In my reading, your approach seems reasonable, i.e., take a file from wherever and bring into Dataverse. Clearly the Excel path makes sense, and you could connect to other databases to bring data into Dataverse. I don't know how much automation you need. I'd think either of the paths you lay out, i.e., Sharepoint -> Dataverse, or simply Dataverse would work well. I'd choose the shortest path, myself.

Use “Edit in Excel” in Dataverse for Teams | Microsoft Power Apps

You should be aware that Dataverse doesn't appear to be a place where you could store more and more data over time. Books I am reading suggest there are limits beyond which Power App and workflow performance suffers.

My counsel based on basic understanding is that there are data bits that you could legitimately store in Dataverse over time because it is reference data, or because it isn't a BIG DATA set, or because you refresh it enough to keep it current (implying pruning of unneeded data).

One thing that seems quite appealing is that Connectors to databases, whether in Azure or AWS, or even in your own company walls (via data gateway) can be used to store and manage the larger data sets, and what you need can be brought into Dataverse for just the time you need it. You may need to upgrade your license.

Also note there appears to be a fee for GBs of data in Dataverse. I've read it is 1 GB free, thereafter a $48/month fee per 1 GB additional. Encouragement to keep data pruned, or to get a server somewhere to keep your larger data sets.

Good luck!

I'm glad to have found this post. I'm trying to help a budding organization move from cloud files that are siloed across departments into a database solution. I have basic SQL experience, but I'm not in the tech department at this organization and have no voice in terms of developing a SQL server or spending any funds on a technical solution. So I've been toying with Dataverse in the hopes of building a database by pulling cloud files (i.e., Excel mostly) into tables that can be called on in Power Apps and Power BI dashboards. Is this the best route, or is there another solution that could work as a "database" in Office 365?

Other considerations:

- Organization is non-profit

- Dashboards and Power Apps must pull from a data source that is not permission-based so that anyone (e.g., Board of Directors) can access the final dashboards, whether or not they have access to the underlying data.

Do I need to go this route?

* Excel (in the cloud)--> Sharepoint List? --> Dataverse --> Power Apps/BI

Or can I go this route:

Excel (in the cloud) --> Dataverse --> Power Apps/BI

Thanks in advance for any advice.

Kate

It would seem that No-Code/Low-Code is not a myth if you separate front end from back end. What isn't discussed in this article or others like it is that ability to create a separate/custom connector, essentially permitting the back end to be what it needs to be behind an API boundary, and the API fitting into the Power Platform world ... allowing the low-code and no-code benefits.

I don't think anyone would say that no-code/low-code is the hammer for all nails, but it has its place. And separation of concerns allows such. Think JamStack and you're on the right track.

With this said, given use of SQL under the sheets, it would be nice to be able to create a Dataverse table set with a SQL script. Just my humble opinion.

Thanks@PaulD1for your measured review of this fanboy posting. I also come from a SQL/C++/COM/C#/Win32 etc etc background and find Model-Driven/Dataverse a restraint to productivity, not an aid. So sick of hearing about this low-code myth - my experience is definitely that low code "developers" rapidly hit the wall when complexity/security/performance issues swamp their abilities and they inevitably call in a grown-up to fix the mess.

I work for a large multinational which has let some very clever (but non comp-science background) staff build some PowerApps and Flows for number of sandboxed business scenarios. Without exception, the Apps that haven't required the intervention of grown-ups have been glorified MS Forms style apps. i.e. a bunch of data collection screens, some pretty geo-location summaries, a suite of dashboards etc. The sort of thing that litters Microsoft's sample galleries.

As soon as anything touches real-life data (SQL, REST integrations with our LOB apps, etc etc) they rapidly fall off a cliff. Complex joins, JSON manipulation, translating external data to what PowerApps can support (e.g. no bitwise operators, so bitfields need to be mapped to string flags etc).

DataVerse: Why the hell would I want to spend days shoe-horning complex normalised data into pre-canned schemas that I then have to fight to make my views work the way they do in SQL without any trouble? I agree, all the "plumbing" effort to get an Azure SQL instance spun up, secured and connected is a pain in the ass, but they should be working to make this easier to do via templatization. And they should also fix their solution support for AAD SQL which doesn't support environment connection variables yet!

Over half the SQL Views in our latest application are solely there to optimise performance in the PowerApp by minimising client-side/callback joins and making drill-down lookups much faster. - Things that wouldn't even occur to a low-code "developer".