cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonykiefer
Advocate I
Advocate I

Show activity log for each selected item

I'm building an app that has one-to-many sql relationships.  The primary screen is a list of records.  These records are populated by the db table Item Master(image provided at bottom of post).

Home List View.jpg

When the right arrow is clicked the user navigates to the "view record" screen.

View Item View.jpg

Note "620" is the itemID from the first screen.  When I click the "Activity Log" button I come to the "Activity Log" Screen.  These items are populated by the db Transaction table(Image provided at bottom of post).

Activity Log View.jpg

Every item on the first screen has an activity log.  The activity log should only show the log records of the selected item.  Naturally this should be applied to the onselect attribute of the "Activity Log" button on the second screen.

 

How can I achieve this?

 

Here is a snapshot of the sql db.

db snapshot.jpg

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@tonykiefer 

Your Items property of the Transactions/Activity Gallery needs to be based on the ItemTracker_Transactions data source, not the Master.  It is however, Filtered by the ItemID that matches against the ItemID that is derived from the BrowseGallery selected ItemID.

 

So, to troubleshoot, look at your Activity table and find a record then note its ItemID.

Then change your Gallery Items property to:

    Filter(ItemTracker_Transactions, ItemID=thatNotedItemIDFromTheTable)

What does that produce?  Does it show the records that you expect from your Table?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

8 REPLIES 8
Liam_Chapman
Resolver II
Resolver II

Hi @tonykiefer ,

 

If I understand correctly, you could make use of a collection OnSelect of the master item/ arrow.

 

ClearCollect(SelectedTransactions, Filter(ItemTracker_Transactions, ItemID = ThisItem.ItemID))

Then reference Selected transactions as the items in your Activity Log table/gallery.

 

Thanks,

Liam,

RandyHayes
Super User III
Super User III

@tonykiefer 

Set the Items property of your Activity Log gallery to:

Filter('[dbo].[ItemTracker_Transactions], ItemID = yourPrimaryGallery.Selected.ItemID)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

This partially works...The problem is the selected item is only showing the common values between the Master table and the Transaction table.

Filtered Activity Log.jpgAll Transaction columns should be present in the Activity Log data tableAll Transaction columns should be present in the Activity Log data table

 

The items property was set to "Filter(ItemTracker_Master,ItemID=BrowseGallery1.Selected.ItemID)"

 

The values ModifiedBy and ModifiedDate are the only two visible fields.

I need to show the entire record from the transaction table.

 

If I change the items property to "Filter(ItemTracker_Transactions,ItemID=BrowseGallery1.Selected.ItemID)" there are no results.

 

Filtered Activity Log2.jpg

 

 

RandyHayes
Super User III
Super User III

@tonykiefer 

If the BrowseGallery1 Items are based on ItemTracker_Master then the formula you need for your other Gallery is going to be:

Filter(ItemTracker_Transactions, ItemID = BrowseGallery1.Selected.ItemID)

 

Your image you have shows errors on all of the columns except the ModifiedBy and ModifiedDate.  So, most likely those text properties need to be adjusted properly for the Transactions datasource column names.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

My Image column names are all in the same format...ThisItem.colName

Column Names.jpg

 

When this record is produced I am using the Filter function as "Filter('[dbo].[ItemTracker_Master]',ItemID=BrowseGallery1.Selected.ItemID)."

 

If I change the Filter function too..."Filter(ItemTracker_Transactions,ItemID=BrowseGallery1.Selected.ItemID)"...then no records are produced.

RandyHayes
Super User III
Super User III

@tonykiefer 

Your Items property of the Transactions/Activity Gallery needs to be based on the ItemTracker_Transactions data source, not the Master.  It is however, Filtered by the ItemID that matches against the ItemID that is derived from the BrowseGallery selected ItemID.

 

So, to troubleshoot, look at your Activity table and find a record then note its ItemID.

Then change your Gallery Items property to:

    Filter(ItemTracker_Transactions, ItemID=thatNotedItemIDFromTheTable)

What does that produce?  Does it show the records that you expect from your Table?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

@RandyHayes...I understand.  The datatable needed the filter function to reference the transaction table per your suggestion.  Filter(ItemTracker_Transactions,ItemID=BrowseGallery1.Selected.ItemID).

 

The I reference a record in the sql db to find a record with activity logs and compared the results.  The results are accurate.

 

I'm not sure why the first attempt and reference the transaction table in the filter didn't work but it is now.

results.jpg

 

I've been working with Powerapps for about 2 months or so and I'm picking up a lot.  Large data sets and 1-to-many AND many-to-many relationship are a bit challenging.  It seems there are a basic set of functions I must learn and as I go.  I thank you for the clarity on this problem as it yet again opened another door for me.

RandyHayes
Super User III
Super User III

@tonykiefer 

No problem at all and keep on learning!  Focus on tables and records, filtering and lookups, and data shaping.  These are all highly valuable skills to have with PowerApps as the functions for them are very powerful.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (64,899)