linking two tables

[sajarac]

Hi there, I have this silly question but I cannot figure out:

I have two tables in my MS Access database:

1. Main Table

2. Projects

Projects and main table are linked by AssetID

I have a Papp with all of the information for my main table, but I would like to include a new screen that show my projects based on the selected value in my gallery1.

BTW, my data is exported into an excel file, the file is in my one drive, and the excel file has two tabs, one for the main "TblMaan" and one for projects "TblProjects"

Can someone point me in the right direction?

Thanks in advance

[sajarac]

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]

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]

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]

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.

[Meneghino]

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]

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?

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

Regards

[sajarac]

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]

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]

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]

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"

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.

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

Regards