Unable to see table in the formula builder
I am trying to build some calculated columns using the formula builder when creating a table in a schema. I am trying to use the lookup function to build the columns. Under my "EBS_TEM_COMMON" scheme i have a table MTL_ITEM_CATEGORIES_V table from which i need to get some data to build few columns in another table.
when i open my formula builder i don't see that table. I tried to go to add datasets and add the table but could not find the table.
Not sure why the table won't showup. Any help is appreciated.
The tables shown in formula builder for creating a formula column are the base table itself and all the parent tables and ancestor on the join path. The lookup function allows us to use any table. The process I typically follow is to test the LOOKUP function based formula on a dashboard insight, and then copy it back to formula column. While we don't see the unrelated in the formula builder, the formula with LOOKUP should still be valid and we can test it again by drop it aside with the insight formula for testing.
Hope it helps.