linking two tables

(0) Share

Report

Posted on by Sajarac

3,259

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

Categories:

General Questions

All responses (45)

Answers (1)

Meneghino 6,949 on at

Like (0)

Report

Re: linking two tables

Hi, I am surprised it is not working at all. There has been an issue in case the search box is empty. What happens if you type something in there?
In any case, if you have a version that works for you, then let it be.
Sajarac 3,259 on at

Like (0)

Report

Re: linking two tables

@Meneghino, good morning and thank you very much for your reply. I've tried your formula but doesn't work, see screenshot, and you are right is taking more time to load data.
Meneghino 6,949 on at

Like (0)

Report
Re: linking two tables

hi @sajarac
glad to see that it is finally resolved.
I think the reason you get the warning is that the Search function is not delegated in SharePoint, so that it will work slowly and only return data from the first 500 records.
However, there is a very valid alternative, the StartsWith function, which is delegated in SharePoint. So please try something like this:
SortByColumns( Filter(MainQuery, StartsWith(Assessment_Name, Proper(TextSearchBox1.Text)) || StartsWith(SRName, Proper(TextSearchBox1.Text)) || StartsWith(AssessorName, Proper(TextSearchBox1.Text)), If(drpfy.Selected.Value="All FY", true, drpfy.Selected.Value=FiscalYear), "Assessment_Name",If(SortDescending1,Ascending,Descending) )
Also, I am somewhat surprised that your code works, since there are a couple of possible errors:
1) The names of columns are in double quotes for the Search function, which means they are taken as text and not the column values. Please note that some functions take the columns names with quotes (i.e. SortByColumns) and some without (i.e. Sort)
2) You have Ascending twice at the end
Verified answer

Sajarac 3,259 on at

Like (1)

Report

Re: linking two tables

Good News, finally it works!!!!

using this formula:

SortByColumns((If(drpfy.Selected.Value="All FY",Search( MainQuery, Proper(TextSearchBox1.Text), "Assessment_Name","SRName","AssessorName"), Filter( Search( MainQuery, Proper(TextSearchBox1.Text), "Assessment_Name"), drpfy.Selected.Value=FiscalYear))),"Assessment_Name",If(SortDescending1,Ascending,Ascending))

I still receiving a warning but is working

Thank you very much for your kind cooperation.

Regards
Sajarac 3,259 on at

Like (0)

Report

Re: linking two tables

Hi, well here is what happened:

I was basically changing fields names in my database to avoid any kind of weird characteres. I have deleted my subsite create a new new one uploaded my two new tables, then in PA I have created a new connection changed all of the field names now I don't have _x0020, etc.

So I check again the sort condition for my gallery and this is what I get:

SortByColumns(If(drpfy.Selected.Value="All FY",Search( MainQuery, Proper(TextSearchBox1.Text), "Assessment_Name","AssessorName"), Filter( Search( MainQuery, Proper(TextSearchBox1.Text), "Assessment_Name"), drpfy.Selected.Value=FiscalYear))),"Assessment_Name",If(SortDescending1,Ascending,Ascending))
Meneghino 6,949 on at

Like (0)

Report

Re: linking two tables

Please try just Main_Query, nothing else.

PS No filtering or sorting or anything. This is because I think the problem is with the name of your data source.
Sajarac 3,259 on at

Like (0)

Report

Re: linking two tables

SortByColumns(If(drpfy.Selected.Value="All FY",Search(Main_Query, Proper(TextSearchBox1.Text), "Assessment_x0020_Name","Assessor_Name"), Filter( Search( Main_Query, Proper(TextSearchBox1.Text), "Assessment_x0020_Name"), drpfy.Selected.Value=Fiscal_x0020_Year))),"Assessment_x0020_Name",If(SortDescending1,Ascending,Ascending))

Result: The function 'Search' has some invalid arguments
Meneghino 6,949 on at

Like (0)

Report

Re: linking two tables

OK, then please just try Main_Query
Sajarac 3,259 on at

Like (0)

Report

Re: linking two tables

Hi,

all of the 3 choices get the same result "invalid argument type (error) expecting a table...."

and just noticed that "SalesRep" was not recognized in my SP list, so I have deleted that field form the formulas but same result.

Going mental now........
Meneghino 6,949 on at

Like (0)

Report

Re: linking two tables

Ok, let's take it one step at a time so see where the error could be. Please try each of the below in turn and let me know where the error comes in.
Dropdown Items seems correct to me.

PS I think the error is that you forgot the _ in Main_Query.

Search(Main Query, Proper(TextSearchBox1.Text), "Assessment_x0020_Name","SalesRep","Assessor_Name")

If(drpfy.Selected.Value="All FY",Search(Main Query, Proper(TextSearchBox1.Text), "Assessment_x0020_Name","SalesRep","Assessor_Name"))
[Select All FY in your dropdown to see the result]

If(drpfy.Selected.Value="All FY",Search(Main Query, Proper(TextSearchBox1.Text), "Assessment_x0020_Name","SalesRep","Assessor_Name"), Filter( Search( Main Query, Proper(TextSearchBox1.Text), "Assessment_x0020_Name"), drpfy.Selected.Value=Fiscal_x0020_Year))

SortByColumns(If(drpfy.Selected.Value="All FY",Search(Main Query, Proper(TextSearchBox1.Text), "Assessment_x0020_Name","SalesRep","Assessor_Name"), Filter( Search( Main Query, Proper(TextSearchBox1.Text), "Assessment_x0020_Name"), drpfy.Selected.Value=Fiscal_x0020_Year))),"Assessment_x0020_Name",If(SortDescending1,Ascending,Ascending))