So I am searching for a solution in which I can copy a certain part from one data table to another.
This is needed as I don't want to edit my main data table which contains all possibility's, I need to edit a different data table so I can use my main data table again later on. This is why I need a way to copy a part of the data table (the part with a certain name in the first column, there are multiple rows with the same name in the first column, but other data in other columns(example attached))to a different data table with a custom ID in the first colum.
This is not really a question, just wanted to share what I found out when experimenting with calculated fields, calculated values, and formula fields.
Basically when you created a formula that contains conditions the system pulls the first record in your table and run those fields in your condition and if that doesn't encounter an error then the formula is considered "valid".
As an example, lets say you have 2 date fields (Date_1 and Date_2) and you used this fields in the formula below
CASE
WHEN [@field:Date_1 ] = [@field:Date_2] THEN 1
WHEN [@field:Date_1 ] < [@field:Date_2] THEN 2
ELSE 1/0
If your source table doesn't have any records the formula will be valid since 'null' is equals to 'null' and the 1st WHEN condition is satisfied.
If your source table have records and Date_1 is equals to Date_2 then the formula is still valid since it satisfies the 1st WHEN condition
If your source table have records and Date_1 is less than Date_2 then the formula is still valid since it satisfies the 2nd WHEN condition
if your source table have records and Date_1 is not equals, or not less than Date_2 then the formula will encounter an error since this will fall to the ELSE condition, and 1/0 is a mathematical error.