cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

LookUp field to perform lookup on entry of data

Hello,

 

I have created an app which has been designed to be able to do some basic data entry which is stored in SQL table.

 

One component of my app is a gallery, however I have set this gallery up in a way that it functions as an editable table. There are 11 columns in total on this gallery and out of those columns I have 4 that I want to use as LookUp columns.

 

When a user types in an order number in column one, these columns lookup to a view, which pulls down various bits of information for the order. The rest of the columns will be manually typed in.

 

Now the LookUp function is kind of working, if I type in an order number and save the record, the lookup columns do pull through the correct fields and values, however this happens AFTER I click save, so the lookup information doesn't actually get recorded to the SQL database. I am assuming this is because the lookup doesn't "calculate" until after that Save button has been clicked and because it calculates after the Save, there is no information to actually record.

 

Here is the Default property of one of my LookUp columns:

 

LookUp('[dbo].[TableName]', works_order = ThisItem.order_no, customer_code)

 

 

As previously mentioned, this DOES work in that it correctly pulls the information, but it happens at the wrong time.

 

What I would like to achieve is that when the user has typed in an order number, the LookUp columns then perform the LookUp to populate the text boxes.

 

I have tried a few different approaches to this but cannot seem to get it working.

 

Not sure if this is the correct way of doing things or if there is a better way but any help would be appreciated.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

@Barney 

 

If I get this right your issue is with "new" items in your gallery. I guess when you click the "+" sign you create a new record in your SQL Table, reload the datasource to show the record in the Gallery. So you have an item in SQL + Gallery where no LookUp column is already set.

 

Your LookUp uses ThisItem.order_no - but as ThisItem reflects your sql data there is no value in "order_no"

 

So when you change it to:

 

LookUp('[dbo].[TableName]', works_order = InputOrderNo.Text, customer_code)

 

it should work as expected.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

8 REPLIES 8
Resolver II
Resolver II

@Barney 

 

could you share some more details and maybe provide a screenshot? What is the datasource of the Gallery? What is your patch statement?

@PowerRanger 

Please let me know if there's anymore information I can provide.


@PowerRanger wrote:

@Barney 

 

could you share some more details and maybe provide a screenshot? What is the datasource of the Gallery? What is your patch statement?


Of course.

The Data Source of the gallery is SQL, the whole app runs with SQL as a backend solution to store data.

 

Here is the patch statement, which only patches a couple of columns right now for testing purposes, but I am happy this works.

 

ForAll(Filter(Gallery2.AllItems, Toggle2.Value = true), Patch('[dbo].[TableName]', ThisRecord, {order_no: InputOrderNo.Text, customer_code: InputCustCode.Text, cost: Value(InputCost.Text)}))

 

Just as an FYI, if I create a record, type in an order no, save the record, refresh the data so that I can see the lookup value, make another change THEN save it again, I can see the lookup value in my database. This is because the previous lookup value is (the value that should have inserted on the FIRST save) is still present.

 

I've attached a screenshot of the gallery. I have censored some information for privacy. The customer code is one of the LookUp fields.

 

@Barney 

 

Yes. As mentioned above please provide some more details. 

 

Thanks


@PowerRanger wrote:

@Barney 

 

Yes. As mentioned above please provide some more details. 

 

Thanks


I've added another comment with the information you've asked for, hopefully this gives you some more clear information.

@Barney 

 

If I get this right your issue is with "new" items in your gallery. I guess when you click the "+" sign you create a new record in your SQL Table, reload the datasource to show the record in the Gallery. So you have an item in SQL + Gallery where no LookUp column is already set.

 

Your LookUp uses ThisItem.order_no - but as ThisItem reflects your sql data there is no value in "order_no"

 

So when you change it to:

 

LookUp('[dbo].[TableName]', works_order = InputOrderNo.Text, customer_code)

 

it should work as expected.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post


@PowerRanger wrote:

@Barney 

 

If I get this right your issue is with "new" items in your gallery. I guess when you click the "+" sign you create a new record in your SQL Table, reload the datasource to show the record in the Gallery. So you have an item in SQL + Gallery where no LookUp column is already set.

 

Your LookUp uses ThisItem.order_no - but as ThisItem reflects your sql data there is no value in "order_no"

 

So when you change it to:

 

 

LookUp('[dbo].[TableName]', works_order = InputOrderNo.Text, customer_code)

 

 

it should work as expected.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.


@PowerRangerAh! It's so obvious now that you have pointed that out.

 

Of course this is the issue, as the order_no has not yet been recorded to SQL it is essentially looking up nothing.

 

I've just made that change, as soon as I input the order_no it populates. Amazing! Thank you so much.

@Barney 

 

Glad to help you.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (8,422)