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]

Am surprise the AddColumns is not working.

Try a collection:

ClearCollect(CombinedTable,Addcolumns('Table1', "Amount", Lookup(Table2, InvestmentID=Table1[@InvestmentID]), Amount)))

Then use the collection CombinedTable

After that take a screen shot of the collection if it does not work as you expect

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

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]

Thanks for the quick response.  Can you clarify the best place for me to create the collection - should it be on the OnVisible function of the screen or in the data items object of the table?

Interesting I have noticed that just using the lookup in a column gives me the last row of the source table for every row and using the addcolumn in the table item field gives me the first row of the source table.  Do you think that the lookup function should also be working directly in the column?

my lookup values are both set as int in the SQL tables is there something special I need to do with the lookup (I tried using VALUE and TEXT on both sides of the equation to force a conversion)

[eka24]

I suggest you it in OnVisible of the Screen.

"is there something special I need to do with the lookup (I tried using VALUE and TEXT on both sides of the equation to force a conversion)" You can use Value or Text as you stated.

However, After creating the collection, give a screenshot of the collection to explain further

[gclifford]

[dbo].[Compliance_OutsideInvestments]=Table1 from previous example

[dbo].[Compliance_OutsideInvestmentsTrack]=Table2 from previous example

Screen On Visible contains

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

Combinedtable under the collections page shows all of the columns I expect (all of Table1 + the extra column from Table2) but no data at all now so the data table is blank.

Screenshot of the underlying SQL tables attached.

Also - here was the exact contents of the Items field when trying the addcolumns

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

Appreciate your help!

[eka24]

After placing the formula OnVisible, you need to move to a different Screen for it to collect the data. Did you navigate to another screen and back to the screen with the Onvisible?

If you are not sure, put the button OnSelect of a button and click. works same way

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

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]

sorry - that was a silly mistake - so now I have data but i have the same problem again - all rows show 5000 as the amount (see attached)

I should be seeing

Investment 1 = 5000

Investment 2 = 2500

[eka24]

Try this:

ClearCollect(CombinedTable,AddColumns('[dbo].[Compliance_OutsideInvestments]', "InvestmentAmount",
LookUp('[dbo].[Compliance_OutsideInvestmentsTrack]',Trim(InvestmentID)=Trim('[dbo].[Compliance_OutsideInvestmentsTrack]'[@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]

Refreshed by changing screens after updating the Onvisible with your code - same result - all rows show 5000 still

[gclifford]

I tried this through the Gallery using ThisItem and the lookup in the same way as the data table and it works fine (see attached)

Really confused on this one