joining tables using collection and addcolumn

[Tbabe]

Hi, i am trying to join two tables from sql, and i want to be able to display records in the detail screen. How do i go about creating the collection and adding column. i have a screenshot below: where do i create the collections(on select , in my submit button where the search takes place? or on visible? i will like my column to display on the detail screen. 

[RandyHayes]

@Tbabe 

What is it that you are trying to create a collection for?  Can you provide more details on what it is used for?

Unless there is a particular need for another duplicate in memory database of your data, you can just put the formula you need in the place you are trying to provide data.

[Tbabe]

ok,  what i am trying to do is to add another table to my datasource, how do i go about it? ClearCollect(Collection1,AddColumns(WorkOrders,"Jobs",LookUp(Jobs,"job_address1_Column1" = WorkOrders[@job_address1])))

[RandyHayes]

@Tbabe 

I'm going to need more information!  There are a hundred ways to go about it.  Are you trying to add columns to your existing table that have a table of other records, or a lookup of certain records related to one record, or are you trying to combine two tables of non-related records into one?

From the formula you provided, it appears that you want to perhaps create a column with related workorders.

I am not sure of the collection need, so the formula to have a column of related records from another datasource would be:

  AddColumns(WorkOrders As _item, "Jobs", LookUp(Jobs, job_address1_Column1 = _item.job_address1)

This is going to give you a table of the records in WorkOrders with a new column called Jobs that will have a table of the records from Jobs that relate to the address.

[Tbabe]

yes, i am trying to add columns to an existing table(WorkOrder) from Jobs table, the tables are related by the (Job_address).