Looking to populate a form with values from one excel sheet based on a field present in another screen and excel sheet.

(0) Share

Report

Posted on by MadhuVadlamani

My app needs to pull data from 2 different data sources( both are excel sheets).

There is a customer ID that is present in both excel sheets that can be used to match.

Some screens already display data from one excel sheet.

I need to now create another screen with forms to display data from the other excel sheet for the data displayed in the other screens for the same CustomerID.

I am looking to use Patch functions and set function to stor

Categories:

Building Power Apps

I have the same question (0)

All responses (17)

Answers (0)

Sort by

WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MadhuVadlamani ,

Assuming you are selecting the second gallery item from one based on the first table, the Items of the second gallery would be

Filter( Table2, CustomerID = Gallery1.Selected.CustomerID )

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.

Was this reply helpful? Yes No
MadhuVadlamani 46 on at

Like
a
(0)

Report
Copy link

Link copied!

Would this not require a patch function or collections?

Was this reply helpful? Yes No
MadhuVadlamani 46 on at

Like
a
(0)

Report
Copy link

Link copied!

So, I have 2 excel sheets, Health and the other Renewal. I have a screen that populates all the columns of the Health excel sheet. I am supposed to create a screen from the Renewals excel sheet. The one column common to both is the CustomerID. So when the user selects a customer on the screen connected to the Health excel sheet, the data corresponding to the same CustomerID in the Renewal sheet must get populated on the other screen.

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@MadhuVadlamani ,

When you say "populate", the code I gave you will populate a gallery with the selected items - are you meaning something else?

Was this reply helpful? Yes No
MadhuVadlamani 46 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Warren, So based on the customer selected in the first(initial) screen, my form in a different screen should show data from a different table. The only link between the 2 data sources(tables) is one column, Customer ID.
By populate, I meant show the values in the new form and not create a gallery.

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MadhuVadlamani ,

You want a new record form showing the items from the second list matching the gallery selected ID from the first list?

You could set a Variable on the OnSelect of the gallery before navigating to the form screen (assuming the field name CustomerID here)

Set( gblNewRecord, LookUp( YourSecondList, CustomerID = ThisItem.CustomerID ) )

Then the Default of each control in the form would be

gblNewRecord.FieldName

with the field name for the particular control.

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.

Was this reply helpful? Yes No
MadhuVadlamani 46 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey Warren, thank you for this. For this solution to work, should I have to directly navigate from the gallery screen to the form screen or can there be interim screens? Will the variable remember the data for a long time?

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @MadhuVadlamani ,

A Global Variable will remain with its value throughout the session unless you change it to something else with another Set() command.

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.

Was this reply helpful? Yes No
MadhuVadlamani 46 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Warren, thanks for the reply. I have attached 3 screens here that should give a better idea of what I am trying to do. The "first screen with the gallery", this screen needs to navigate to another screen, "the interim screen" and also set the variable for storing the customer ID. Is it possible?
From the "interim screen", I have added a Contract button that navigates to the Target screen where there is a form where I want to populate the information using the global variable.
I have tried the solution you gave me earlier, but no data is displayed on the target screen. Could you please help? Appreciate your patience on this.

Was this reply helpful? Yes No
WarrenBelz 156,456 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@MadhuVadlamani ,

Yes - all of that is possible, but your screen shots really did not help explain the issue in any more detail. You can simply set another Variable to the Customer ID when exiting the Interim screen. If you cannot manage this, please supply the code you are using (in Text) with a bit of an explanation of the relevant form, screen, list and field names.

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.

Was this reply helpful? Yes No