UI builder - Show colums from a different table

[joshmorris]

Hi, so as shown in the photo below what I am trying to do is show the expiry date of the contract as a column when viewing vendors. However I am not sure how to do this as the data is stored in the contract table and this is showing data from the company table.

[IronPotato]

I'm not able to show but here's logic behind it.

since you need display data from 2 different tables you need to "JOIN" them together in the database. That's what database views are. So you are basically creating 3rd table where you are joining columns together. Once that is created (just follow the instructions in URL), instead of providing your 1st or 2nd table in "List" component in UI Builder, you will provide it with your newly created table.

[joshmorris]

Thanks,

I am not sure on what to put for the where clause. That is where I get confused

[IronPotato]

WHERE clause in SQL means filtering so that's where you should specify your query so that records are bind correctly. So your 1st table should have some reference field into 2nd table. Example for that is country=Brazil (CONDITION says give me for example all records WHERE country field is Brazil);

I recommend SQL knowledge when working with SN as it's all about that. Records in rows and columns and tables in SQL database. Nothing less nothing more.

[joshmorris]

I have no prior knowledge to SQL so if I was trying to show contract expiry date on a vendor what would I put the where clause as? My 2 tables are core_company and ast_contract. Sorry to be a pain but im new to all this.

[joshmorris]

In fact I have now done this correctly however when selecting the newly formed table on the UI Builder it does not show up. However it shows when I select the table on reports