Getting Lost in Searching and Other Things

[dynamictiger]

Hi

I am new to powerapps and so far going okayish.  I have a fairly complex SQL backend I am connecting too with a lot of normalised data in it.  The app I am working on, and is near complete is to add new jobs.  So if a client asks one of our staff any of them can book a job then and there.

I have been following google a lot to get help on this, and occasionally come here when I am a bit lost.

I am struggling around a couple of functions on the Browse Gallery.

First as the heading suggested I am getting lost on how to search.

The powerapps default search is:

SortByColumns(Search([@'[dbo].[Job]'], TextSearchBox1.Text, "JobDescription","JobNumber","OrderNumber"), "JobDescription", If(SortDescending1, Descending, Ascending))

Looking at this I see it is saying sort by column job description ascending or descending according to the selection.  With a search enclosed being search dbo.job on the text entered against JobDescription, JobNumber or OrderNumber.  So far so good...except thats not going to help me in the field.

Instead what I need to search on is SiteName.  However SiteName is a foreign table to dbo.Job.  dbo.job contains SiteID so I got my thinking out and tried it out and got to

Search([@'[dbo].[Job]'], TextSearchBox1.Text,LookUp('[dbo].[Site]',SiteID=ThisItem.SiteID,SiteName),"JobDescription","JobNumber")

which is kind of a hybrid between the lookup for the drop down and the search.  However I dont think this will work as it has the ThisItem in there?  Or am i being a bit overthinking?

My second mystery of the day...I only want to display jobs not yet invoiced.  In the underlying table there is a field called InvoiceDate.  If InvoiceDate is null then display the job I guess that would be something like????

SortByColumns( "JobDescription" IsNull("InvoiceDate"), If(SortDescending1, Descending, Ascending))

But really I have no idea.  I would like to see these separately so I can see how the language works.

[Anonymous]

For your first question, are you selecting a Site from a gallery? What's the lead-up to the second function you are showing with the ThisItem call?

For your second question, it would be IsBlank, not IsNull. Also, you need to add a Filter function around that. Like this:

SortByColumns( Filter({YourJobTable}, IsBlank(InvoiceDate)), If(SortDescending1, Descending, Ascending))

[dynamictiger]

Thanks so far so good and learning...

SortByColumns(Search(Filter([@'[dbo].[Job]'],IsBlank(InvoiceDate)), TextSearchBox1.Text, "JobDescription","JobNumber","OrderNumber"), "JobDescription", If(SortDescending1, Descending, Ascending))

[Anonymous]

Good to hear! Did that clear up your first question or are you still stumped somewhere?

[dynamictiger]

Not following the first part at all.   How is this related?

[Anonymous]

How do you intend on the end user selecting a Site for this function? If you're using a combobox, then you'll want to do something like this:

Search([@'[dbo].[Job]'], ComboBox1.Selected.SiteID,"SiteID")

If they select it from a gallery, then it will be like this (basically the same):

Search([@'[dbo].[Job]'], Gallery1.Selected.SiteID,"SiteID")

[dynamictiger]

Oh sorry, I didnt realise I hadnt clarified this.

I am using a text only search as the other searches may be useful.

[dynamictiger]

Sorry that wont work I don't think.

In gallery I am displaying the 'Site Name'.

Bell Tower, land Aquatic Centre etc are populated by looking up to a foreign table from the Site ID value stored in dbo job.

What I am wanting to do is search Be (for example) and return job 12345 for Bell Tower in this small example set of data.

My challenge is how do I search for text being 'be' to find Bell Tower in dbo Site and return the records on the Browse Gallery from dbo.job?

Search([@'[dbo].[Job]'], TextSearchBox1.Text,"SiteName","JobDescription","JobNumber")

Would likely work if Site Name was stored in dbo Job.  

Therefore I thought

Search([@'[dbo].[Job]'], TextSearchBox1.Text, LookUp('[dbo].[Site]',SiteName=TextSearchBox1.Text,"SiteID"),"JobDescription","JobNumber")

Would work.  But it thinks I'm ugly and my mum dresses me funny and wont co-operate?

[Anonymous]

No worries, so you have a text search and want to lookup a job based on the text value of a site, but the job datasource only has a siteID? That would look something like this.

Filter('[@'[dbo].[Job]'],SiteID=LookUp('[dbo].[YourSiteDataSource]',SiteName=TextInput1.Text,SiteID))

[dynamictiger]

Hi Eric

Thanks for the help so far, but I dont think this answer is correct.  Perhaps I am misunderstanding something, wouldnt surprise me.

The proposed 

Filter('[@'[dbo].[Job]'],SiteID=LookUp('[dbo].[YourSiteDataSource]',SiteName=TextInput1.Text,SiteID))

is based on a filter.  As I understand it this will filter the recordset to only show records matching the site name.  Thats okay.  However the original search text:

SortByColumns(Search(Filter([@'[dbo].[Job]'],IsBlank(InvoiceDate)), TextSearchBox1.Text, "JobDescription","JobNumber","OrderNumber"), "JobDescription", If(SortDescending1, Descending, Ascending))

was filtering the job file by items not having an invoice date and then searching on the text in the text box.

Again as I read it the search was across JobDescription, Jobnumber and OrderNumber.  As different people think differently I was wanting to retain the ability to search JobDescription, Jobnumber and add the ability to search the SiteName.  I dont see how I can integrate the two pieces of code to achieve this outcome?

Perhaps I have asked the question badly, I apologise if I have.  I thought splitting in my mind would make this easier to understand for me.  I end up with the following after thinking about it a lot...

SortByColumns(Search(Filter([@'[dbo].[Job]'],IsBlank(InvoiceDate)), TextSearchBox1.Text, "JobDescription","JobNumber",(SiteID=LookUp('[dbo].[Site]',SiteName=TextSearchBox1.Text,SiteID)), "JobDescription", If(SortDescending1, Descending, Ascending))

But other than making loverly red squiggles it doesnt help me...Grrr what am i missing?