cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sajarac
Level 8

Re: linking two tables

Thank you very much for your prompt reply.

 

I am going mental with this conversion, it will take time but I can do it.

 

So basically I don't mind to start from scratch, basically this is my situation:

 

I have a database in MS_access with lots or relationships. and also I have a couple of querys. I have exported two querys to excel them using the "export table to share point list" I was able to see 3 tables or list:

 

Assessments

Locations

Fiscal Year

 

So now here are my questions:

 

1. Can I export from MS_Access query direct to any folder in my share point site? if so how can I achive this

2. once the querys has been exported is any way to keep a link beetween those tables, I don't want to copy paste every time that I update my MSAccess database. 

3. Why the previous tables exported form my excel table doesn't show the option to create a powerapp ?

 

 

 

Meneghino
Level 10

Re: linking two tables

Hi @sajarac,

do you want to continue to use Access?  You could possibly try to create the SharePoint lists as external lists, and update the content from Access from time to time, but it will be quite messy and will be prone to failure.

Creating relationships between SharePoint lists is not a problem (use lookup type columns)

What do you think you need Access for?  This is an important question to understand if SharePoint lists are really what would resolve your problems or would create more.

I fully agree that copy-pasting is not an option for keeping data synchronised between two tables, it is only ever an option for a one-off migration of data.

 

sajarac
Level 8

Re: linking two tables

Hi@Meneghino, Yes I would like to continue using MS Access, I have a bunch of reports plus many hours iinverted so I don't want to kill the project. plus I am used to. Share Point for me is totally new. saying that I don't mind to update the contect from Access from time to time.

 

Now I was able to export direct from my MSAccess query mine two tables "Assessments" and "Locations"

 

but again if I start migrating my 12 apps that I have so far linked with my one drive I will get a mess folder, that is what I would like to have those tables in a separte folder properly tagged.

 

after this I will recreate the connection.

 

What do you think?

sajarac
Level 8

Re: linking two tables

I was able to insert my two querys straight from my MSAccess to SP in a subsite, then I've recreated a new connection to those two tables. so far so good. 

 

If I change the galley items to my Assessment table I can see all of the records, but for some reason the sort by columns is not working and also the pie chart adn bar chart.

 

Getting closer.

 

Capture.PNG

Meneghino
Level 10

Re: linking two tables

Excellent, please get back if you need a hand.

I would still consider fully migrating away from Access, since you will probably have to sooner or later.

Cheers.

sajarac
Level 8

Re: linking two tables

Thanks you very much, finally figured out and for wure I will swithc forom onedrive to SP, now I have 4 specific question to complete this task:

 

1. My gallery1 was sorted before using the value selected in my dropdown field "drpfy" and the textSearch1.

2. The two charts I cannot get the right data

3. How can I get the sum of the filtered values in my gallery4?

4. last one and probably most important: next time that I need to update my data from MSAccess is just repite the process of export my query to SP, or do you think that is better copy and paste?

 

Capture1.PNGCapture2.PNGCapture.PNG

 

and again thnak you very much for all your support!!!

 

Regards

sajarac
Level 8

Re: linking two tables

Please forget item No.2 "2. The two charts I cannot get the right data"

 

I've found the solution.

 

But I need your help with the other 3 items

 

Thanks again

sajarac
Level 8

Re: linking two tables

Hi please forget item: 2  and 3 already solved

 

I've just test again and exported my query so I get in SP same table plus _1 i.e Project Locations and Project Location_1. I tought that just if I delete the first one and rename the second one to the first name the connection will work but no. I had to create a new connection, so I guess the only choice is to copy and paste?

 

So please kindly answer questions 1 and 4

 

1. My gallery1 was sorted before using the value selected in my dropdown field "drpfy" and the textSearch1.

2. The two charts I cannot get the right data

3. How can I get the sum of the filtered values in my gallery4?

4. last one and probably most important: next time that I need to update my data from MSAccess is just repite the process of export my query to SP, or do you think that is better copy and paste?

Meneghino
Level 10

Re: linking two tables

Hi @sajarac

 

Glad to see that you are making good progress.

 

Unfortunately, as I think I mentioned in one of my other replies in this thread, you cannot re-use list names in SharePoint, as they will be assigned a new name, so that you have to re-create the connection every time.  Until then the best option is to copy-paste the current data on top of the old data in the existing SharePoint list.  However, I cannot recommend this as a long term solution, I would urge you to consider fully migrating to SP or another structured database as soon as practical.

 

Now to your specific questions:

1. My gallery1 was sorted before using the value selected in my dropdown field "drpfy" and the textSearch1.

 

If you look at the items property of Gallery1 (thank you for the screenshot), you see this:

Search('Main Query', TextSearchBox1.Text, "Assessment_x0020_Name", "Assessor_name", "Fiscal_x0020_Year")

You see that there is only code for filtering (which should work) but not for sorting.  As I don't know which column you are trying to sort and what type of values drpfy returns, it is difficult to give you specific code, but you can probably find it in the Items property from the version where it did work.  In an case it will be something a bit like this:

Sort(Search('Main Query', TextSearchBox1.Text, "Assessment_x0020_Name", "Assessor_name", "Fiscal_x0020_Year"), MyColumnIWantToSortBy OR SomeFunctionOfdrpfy, asc or desc or some function)

4. Covered in the intro.  If you don't have tables with >5,000 rows then SharePoint lists may be a good solution.  If you have tens of thousands then you may want to consider something like Azure SQL DB, I am becoming a fan.

sajarac
Level 8

Re: linking two tables

Good Morning @Meneghino, thank you very much, I have been working and working with this project. is almost done, I've found that so far the easy way to sync my data between MSAccess and SP was to import and link the two tables, then I just copy and paste and then refrest the SP list. so far I am ok with this. thanks again for your advice.

 

At this point I only need to finish the gallery sort and after this we are ready, so let me show you what I had before with my one drive and what I have now with my share point:

 

1. My app connected with my one drive, as you can see sort function works great and I am able to sort using my drop down "drpfy"

Capture1.PNG

 

2. Now this is the new app, connected with share point list. I have replicated the same formula and changed the fields name to match SP list, but for some reason I can't get it to work, and even the other formula proposed.

 

Capture2.PNG

Could you please let me know what I am doing wrong? and thanks in advance for your kind cooperation

 

Regards

 

 

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,984)