DataTable pull data from another table

[gclifford]

Hello,

I have an SQL table

Table=Table1

InvestmentID

Name

Test1

Test2

Which I am showing in a datatable object

I have another table

Table=Table2

RecordID

InvestmentID

Amount

Date

I am trying to look up the Amount from Table2 and include it in the datatable showing the data for Table1.  I have tried

1. Datatable is set to Table1 as its source.  Adding a column to the data table and replacing the text field with Lookup(Table2, InvestmentID=Thisitem.InvestmentID, Amount) . This does pull in the first row of table2 and shows the amount but it shows the first row of Table2 for all entries in Table1 - it doesn't appear to be changing "Thisitem.InvestmentID" as the row changes in the table.

2. Using the AddColumn function in the datatable items field.  Addcolumns('Table1', "Amount", Lookup(Table2, InvestmentID=Table1[@InvestmentID]), Amount).  I get the same result as attempt1 above

What am i doing wrong??

Bonus question if the above is solved.  If there are multiple rows in Table2 for each investment in Table1 how would I return only the row with the most recent Date?

Regards

G

[eka24]

Did you try the Trim formula I gave

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[eka24 · Accepted solution]

I realised you are refering to same data twice:

ClearCollect(CombinedTable,AddColumns('[dbo].[Compliance_OutsideInvestments]', "InvestmentAmount",LookUp('[dbo].[Compliance_OutsideInvestmentsTrack]',InvestmentID='[dbo].[Compliance_OutsideInvestmentsTrack]'[@InvestmentID],InvestmentAmount)))

use:

ClearCollect(CombinedTable,AddColumns('[dbo].[Compliance_OutsideInvestments]', "InvestmentAmount",LookUp('[dbo].[Compliance_OutsideInvestmentsTrack]',InvestmentID='[dbo].[Compliance_OutsideInvestments]'[@InvestmentID],InvestmentAmount)))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[gclifford]

That was it! - thank you for your help.  It looks like the AddColumns method also works now!

Last question - Table2 (OutsideInvestmentsTrack) will eventually have multiple rows for each investmentID.  What is the best way to return only the row with the latest ReportDate? do I have to use a Sort and Filter instead of a lookup?

also - any thoughts on the simple lookup (replacing the Text field on a column) doesn't work

LookUp('[dbo].[Compliance_OutsideInvestmentsTrack]',InvestmentID=ThisItem.InvestmentID,InvestmentAmount)

This gives me the same result for each row

[eka24]

Using Sort function should work for you. 

First(Sort(CombinedTable, DateColumn, Descending))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[gclifford]

The combined table won't have two entries so the filter/sort won't work there - I need to do this sort on Table2 before the lookup is performed and it doesn't appear to like that

OutsideInvestmentsTrack needs to be filtered or sorted to show just the most recent entry by date for each investmentID then it can be merged in with OutsideInvestments where InvestmentID = InvestmentID

Any thoughts?

[gclifford]

I tried doing this to sort the OutsideInvestmentsTrack first before doing the match but it gives a weird gateway error

AddColumns('[dbo].[Compliance_OutsideInvestments]', "InvestmentAmount",LookUp(Sort('[dbo].[Compliance_OutsideInvestmentsTrack]',ReportDate,Descending),InvestmentID='[dbo].[Compliance_OutsideInvestments]'[@InvestmentID],InvestmentAmount))

Any ideas?

[eka24]

Please what is the objective of the sort?

What about warping the whole formula with the Patch instead of the middle of the Formula

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[gclifford]

OutsideInvestmentsTrack contains two rows for a particular InvestmentId

InvesmentID, ReportDate, InvestmentAmount

2,2020-01-01,2500

2,2020-03-30,1500

Currently the first row is returned - I need the second row (the latest) to be returned when I am using the AddColumns to pull in the Investment amount to the parent table OutsideInvestments

Can you elaborate on the Patch?

[AbdulMatheen]

I'm reaching out to seek assistance regarding an issue I'm encountering in PowerApps, specifically with the use of two tables via collections.

To provide some context, I have two tables: DataTable1 and DataTable2. DataTable1 contains a collection of values retrieved from a SharePoint list, consisting of two columns and 100 rows. On the other hand, DataTable2 contains a collection of values from individual entries, with only one row. However, I require the data from the two columns of DataTable1, which contains 100 rows.

Unfortunately, I'm facing challenges in effectively integrating the data from DataTable1 into DataTable2. Despite my efforts, I haven't been able to achieve the desired outcome.

Could you please provide guidance or suggest a workaround to efficiently merge or incorporate the data from DataTable1 into DataTable2 while maintaining the integrity of the 100-row structure?

Your assistance in resolving this matter would be greatly appreciated. Thank you in advance for your support.