0

V Lookup between 2 tables

Hi I have a table called Reports which has text field called Column 1. The string "1" is entried in the Column 1 text field. Theres also a formula field in which I would like to lookup the Risk description in the Risk List table.

I would like to lookup the risk description of where the risk number (stored as a string) is equal to the string stored in Column 1.

Then I used this code:

let 'Column 1' := 'Risk List'.'Risk Number';
(select 'Risk List' where 'Risk Number' = string('Column 1')).'Risk Description'

Theres nothing wrong with the code, .. no errors, but is just showing me a blank. Do you know why this formula is showing a blank? Risk Number, Column 1, and Risk Description are all entried as a string, and there is definitely a risk description for the risk number (which is equal to column 1)

let 'Column 1' := 'Risk List'.'Risk Number';
(select 'Risk List' where 'Risk Number' = string('Column 1')).'Risk Description'

I need your help please..

1 reply

null
    • Fred
    • 1 yr ago
    • Reported - view
    let 'Column 1' := 'Risk List'.'Risk Number';
(select 'Risk List' where 'Risk Number' = string('Column 1')).'Risk Description'

    Just to clarify, you have a reference field in Reports to Risk List, that is what line 1 says to me.

    If so then you don't need line 2. Use the power of relationships to get the data you need.

    Try something like:

    let crtn := urlDecode("%0A");
let t := this;
let x := 'Risk List'['Risk Number' = t.'Column 1'].'Risk Description'
join(x,crtn)

    Line 1: sets a variable that stores a carriage return

    Line 2: sets a variable of the current record

    Line 3: sets a variable that filters the reference field 'Risk List' to filter the Risk Number by what is in the value of the field Column 1, then gathers all of the data in the Risk Description field.

    Line 4: uses the join() command to take all of the filtered data from line 3 and instead of a comma between data sets there is now a carriage return making it easier to read.

Login to reply

Content aside

  • 1 yr agoLast active
  • 1Replies
  • 42Views
  • 2 Following