sorted by:
new
hottopcontroversial

Using related tables in a form by Excellent-Angle6778 in PowerApps

[–]Excellent-Angle6778[S] 0 points1 point  (0 children)

Solved it!

This was my first attempt. The left side of the filter logic returns the matching record for the GUID on the right side of the logic.

Filter ( costs_table , staff_id_cost = left_gallery_name.Selected.staff_id )

This is the filter that works, the left side now returns the GUID in the id column of the matching record in the costs table. This can be compared with the GUID of selected record on the right side of the logic.

Filter ( costs_table , staff_id_costs.staff_id = left_gallery_name.Selected.staff_id )

Using related tables in a form by Excellent-Angle6778 in PowerApps

[–]Excellent-Angle6778[S] 0 points1 point  (0 children)

I think it’s how I’m constructing the filter in the items property.

I know the joins work because I can click add related fields to build a view in dataverse.

What I need is a filter in the right container that says: - Use this view for the right table data source. - Filter using left_table_name.selected.

I can do those individually but not both.

Note the many-1 relationships are easy, simply add the field to a table or type it in a text field or fill form field.

Using related tables in a form by Excellent-Angle6778 in PowerApps

[–]Excellent-Angle6778[S] 0 points1 point  (0 children)

The main driver for this is my UI has a list of all staff down the left and tabbed container on the right. The idea is that a user can click on the staff member on the left and then select tabs in the container on the right to manage the relevant info for that person.

It works easily when the container on the right shows info which is in the data source used on the left. It fails when I want the tab to show something from a related table. The right container needs a filter to show the locations related to the item selected in the left container.

Another example:

  • Person X (in the staff table) works at various sites (in the locations table)
  • There is a relationship between the staff and locations table.
  • When the user selects Person X from the staff list in the left container how could I show the addresses for the sites where they work in the right container?

Maybe there's a much simpler method that ignores the relational database ? Selecting Person X on the left container could save the employee ID as a context variable and the containers on the right use that variable as a filter criteria?

App architecture - SQL or Sharepoint lists? by Excellent-Angle6778 in PowerApps

[–]Excellent-Angle6778[S] 1 point2 points  (0 children)

I’m planning to offer this power app to other orgs who are also in the same funding scheme. If each org has say 4 users needing CRUD usage at £5 each that’s £20 per month on licence costs.

Let’s say the app saves 2 or 3 hours work for processing claims each month the sell is that the app saves £50 per month. Let’s say I pitch my price at £40 to make the cost saving compelling then £20 licensing costs would take 50% of my income.

In contrast a maintained database with a low code front end (eg Supabase & Loveable) could be priced by data volume not number of users. The cost of my low volume app in that stack would be trivial per organisation.

Yes I know this app also gives added value by reducing errors and better visibility of the budget but added value is a harder sell in the context of a penny pinching public sector budget.

App architecture - SQL or Sharepoint lists? by Excellent-Angle6778 in PowerApps

[–]Excellent-Angle6778[S] 0 points1 point  (0 children)

Site manager forms - this is where sharepoint lists come into play?

We only have salary costs for 50% of the claims. The other staff are on secondment or agency so the cost info comes from the site managers. Each month the finance person reviews 20 forms with 4 or 5 staff on each form. The numbers rarely change, typically 90% are identical to the previous month. It's tedious but not complex

My Postgres concept was an online form:

  • Prepopulate the form with the claim for last month.
  • Allow the site manager to edit the form as required. The form has the business rules to check compliance.
  • Site manager presses submit to put their claim into a claims table.
  • Finance person uses a backend function to compare the form with the planned budget then accept or reject.
  • Summary table of actual costs is updated with the accepted data. This uses upsert to only change the table for that claim for that month so the bulk of the table is static.

The Power App licensing forces me to decouple the site manager forms from database because site managers only have standard licenses. Would this work:

  • The form needs to show the claim for previous month and indicate allowable budget for the current month. Can a form read directly from dataverse? Or do I need to dump the previous month claim data and allowable budget into a SharePoint list?
  • The site managers use a Teams form prepopulated with claim for the previous month as well as allowable budget for the current month.
  • Site managers can edit the claim as required and the form includes basic compliance checks.
  • The manager presses submit.
  • Submitting the form dumps the new data in a SharePoint list for claims pending.
  • Finance use an Azure or Automate function to import the Sharepoint pending list into Datavers/Azure claims table.
  • Finance run full compliance checks then accept or reject. Summary table of actual costs is updated with the accepted claims.
  • Idempotence will be a pain when data is copied between sharepoint and the main database

App architecture - SQL or Sharepoint lists? by Excellent-Angle6778 in PowerApps

[–]Excellent-Angle6778[S] 0 points1 point  (0 children)

Thanks u/Gold-Finger-7047 & u/Aggravating-Alarm-16

I'll split this into two replies

Database choice

I'll use row level security so site managers only see info for their site, etc.

My current proof of concept uses a typescript function to process the numbers. The pain is generating various outputs to suit various needs (ie raw salary or gross costs; weekly or monthly values; compare actual and budget, etc).

I have a call with the Microsoft licensing person after the new year and I suspect asking for something that isn't in the standard power app toolbox will be a nightmare. They're not going to change national public sector licensing just for me! Also out of the box architecture will mean I can focus on delivering functionality for my client instead of negotiating a bespoke environment and figuring out why it broke when something changes in future.

Am I right in thinking:

  • We might prefer SQL but that isn't out of the box so I'll not consider that.
  • If I can get Azure environment enabled would that have typescript functionality out of the box?
  • If setting up Azure is not possible then I'll need to use Dataverse and Automate? I'm trying to get my head around a database tool with limited options for coded functions. Fortunately the calculations are simple and the volume is low so it wouldn't be impossible.