Lookups between two tables connected with my SPL (1 Viewer)

[sajarac]

Hello community. I was wondering if this is possible to do it?

I have an scenario where I get an excel file each month. Every month the file has some updates or change values in some columns. So I need a mirror file where I can keep a copy of the rows plus some information entered by the end users.
I have this working in an excel file file with the primary table "Table 1 Source" and a secondary table "Table 2 Mirror" with some lookups and is working fine. My question is:
Is possible to have the same formulas in two tables connected with my Share Point list? what I mean or trying to do it is: If I have my Sharepoint list in my access file and I copy / paste the source file in the first table the lookups will work?

If so, how can I do this?

Below are some samples on how the data is:

Thanks in advance for someone who can point me in the right direction.

Regards,

 

[Pat Hartman]

Why is Access not the master source for the data? What application is creating the spreadsheets and why is that the master?

You said that a bunch of the columns are lookups? Why? What happens if some new value shows up in the external export? You won't be able to import the record if any of the lookups are invalid.

So, given that your app contains only extra attributes, I would eliminate all the lookups. You shouldn't be changing this data anyway so you have no need for combos to make that easy. Only the unique ID and the extra attributes should exist in your local table. The second table should have its rows deleted and all the data reimported each time you want to refresh it. You will need to remove the relationship from the two tables to make this possible.

After the external file has been replaced, I would do a left join between your local table and the imported data to identify if any rows have been deleted from the import. I don't know what you would want to do if this happened. Then you can do the left join the other way and use that query to add new rows to your local table with empty extra fields.

Forget RI. That only works if you are in control over all the data and you are not. So just find the differences and make a plan for how to deal with them.

 

[sajarac]

Hi, thank you very much for your prompt response. Let me try to explain better all this messy scenario and please apologize for the mess.
The data came from a Azure Active Directory exported as csv file. The data change frequently but for many reasons I can not get a direct connection with that report. That is why somebody is downloading the file each month as a csv.

Now, I am building a PowerApp. Ideally the powerapp should be connected to that Azure Active Directory report. But it is not possible.

With the data another user needs to add more columns or more fields. that information is not possible to send it back to Azure. That is why we need to create a mirror report.

My initial thought was to have two excel tables :
First table is the report that came from Azure.
The second table is a mirror with exactly the same columns plus the additional columns required. I am connecting my powerapp to this table.

The process that I am doing in this moment is: Once I copy/paste the azure report in the first table the last column "IsinMirror" will tell me if the record exist in the mirror table. If the record is not in the mirror app then I manually copy/paste that Equipment ID into the mirror table.

Now because I have in my mirror app all of the columns set as lookups. the values will be updated with the new values from my azure report.

In this way I have the report with the latest updates plus I can add new information to my records such as "Status", "Notes" etc.

The problem that I have is: Excel is not a reliable datasource for my power apps. Excel is poor in terms of performance and I have most of the time delegation issues and limitations.

That is why I was thinking of change this to a Share point list. I mean put the two excel tables into a share point list and create the lookups. etc.
The problem is: Share point is a point for the lookups. I can not do it in the same way that is done in excel.

Finally after al this problem. I thought that if in my MS Access file I get the external data form online services from my Share point list and create the connection with my two lists. then I can add the columns required and create the lookups that I need it. After that I can just copy paste the new values from my azure report. Refresh and done.

Now in my powerapps I can replace my excel datasource for a share point list and problem solve.

Make sense? Sorry if still not clear.

Thanks again.

Regards.

 

[Pat Hartman]

The second table is a mirror with exactly the same columns plus the additional columns required. I am connecting my powerapp to this table.

Merging your local data with the remote data is just making your process more complicated. Keep them separate as I said earlier. That allows you to completely replace the feed without loosing your local data. Use a query to join the two tables and use that as the recordsource for the report.

 

[sajarac]

Would you mind to explain me like a baby steps? Sorry I am good in power apps but very limited with MS Access.

 

[Pat Hartman]

Try reading my initial post again.
1. table 1 has the data from Excel and is totally replaced each time you want to refresh it.
2. table 2 has the unique ID but only the extra columns
3. Join the two tables with a query to see all the data together. Use a left join.

Think about the validation I suggested in my first post also.

 

[sajarac]

Thank you very much for your patience and help. I will follow your instructions and if I get stuck I will come back.
Best Regards.

 

[Pat Hartman]

I know that some design constraints are not always within your control. If taking over the maintenance of the source data is something you can suggest, that would be the best solution. You would give the users a database that holds all the data they need. However, if the data is coming from a different application, then it isn't yours. It is only available for reference so the best solution is to keep it in a silo and never allow changes to it since changes made in your app have no way of travelling back to the source. In fact, if the source data exists in a database, the best solution, if the PTB will allow it is to link to their tables. That gives you always live data. You don't ever have to worry about it being out of date. The DBA can define your permissions so that you have read-only access so there won't be any possibility of accidental modification.