Re: Copy longtext field from one table to an other

bouhme · 05-04-2019 03:24 AM

Is their a way to copy a longtext field from one tabel to an other tabel so that I can edit the field in the new table?
The easiest way would be to make a selection in a enum column, and the corresponding longtext field would be copied to the next column. Than i could edit the copy text.

Mike_A

It seems like you do not care about keeping the data in-sync? not sure your exact use case, but if you want to duplicate a column in another table and be able to edit it, I think you may need to consider using an Action button to do that. Since the column is in another table, have to see if actions can specify a column in another table (I have not tried this yet). There is an app demo’g how to use “Ref Actions” that effect another table so might want to check this out: App Demo: Reference Actions

Jeremy_F

Create a child table, and use dereferences to retrieve the longtext from the parent table. Set the formula on the initial value, and you can edit the long text in the child entry

Mike_A

Nice! Now you have two options!!

Steve

Easy enough. Set the initial value expression for the target column (the LongText column to receive the copied text) to:

LOOKUP([_THISROW].[SourceChoice], "SourceTable", "SourceName", "SourceText")

replacing SourceChoice with the name of the Enum column containing the user’s choice; SourceTable with the name of the table containing the original text; SourceName with the name of the column in SourceTable that contains the Enum values matching the one chosen by he user for SourceChoice; and SourceText with the name of the LongText column in SourceTable that contains the text to copy.

The above will populate the column value with the chosen source text from the source table–but will only do so when the row is first created: once the new row is saved, changes to SourceChoice in future row edits through a form will not change the copied text.

Also note that changing the SourceChoice value will only update the target text so long as the user does not edit the copied text. Once the user edits the text, a change to SourceChoice will not change the target column’s value.

Read more:

Mike_A

Even better details from @Steve … use Initial Value first time around, and later edits can override. Always multiple ways to get the job done

Mike_A

@Steve: another syntax question…
why in the LOOKUP do we sometimes use column names between brackets ([colnam]) and other times in quotes? I am always feeling things are a bit inconsistent. What am I missing?

LOOKUP([SourceChoice], “SourceTable”, “SourceName”, “SourceText”)

Also, why use Lookup vs the DeRef @Jeremy_F suggested? Seems like if the tables are related by key, then the DeRef is automatically available?

Steve

Brackets: an expression to be evaluated to produce an intended value.

No brackets: a name to be taken literally and used as-is.

LOOKUP([SourceChoice], “SourceTable”, “SourceName”, “SourceText”)

[SourceChoice] is an expression that evaluates to the–oops. Need to correct a mistake…

Let’s try this again…

LOOKUP([_THISROW].[SourceChoice], "SourceTable", "SourceName", "SourceText")

[_THISROW].[SourceChoice] is an expression that evaluates to the value in the SourceChoice column of the row referenced by the _THISROW (pseudo-)column. The first argument of LOOKUP() is taken as an expression to be evaluated for each row in SourceTable. If the first argument were just SourceChoice (i.e., LOOKUP(SourceChoice, ...) or LOOKUP("SourceChoice", ...)), LOOKUP() would look for the literal text, “SourceChoice”, not the current value of the SourceChoice column.
SourceTable is the name of a table to look in and cannot be an expression.
SourceName is the name of the column of SourceTable in which to look for the value produced by the expression in the first argument. It cannot be an expression.
SourceText is the name of the column in SourceText whose value should be returned if a match is found. This, too, cannot be an expression.

It may also help to consider the LOOKUP() and equivalent SELECT() expressions:

LOOKUP([_THISROW].[SourceChoice], "SourceTable", "SourceName", "SourceText")
ANY(SELECT(SourceTable[SourceText], ([SourceName] = [_THISROW].[SourceChoice])))

In the SELECT() expression, you would not be allowed to use expressions in place of SourceTable, SourceText, or SourceName. Likewise in LOOKUP().

Mike_A

@Steve - thanks for taking the time.

maybe it’s just me (and the way the syntax is listed in the help docs).

Syntax

LOOKUP( find-value , in-dataset , in-column , return-column )

when I read “in-column” I am not thinking in-column-named, I am thinking in-column.
so… I would first think

LOOKUP([column with the value I want to find],“tablename”,[ColToSearchIn], [ColToReturn])

when it actually needs to:
LOOKUP([column with the value I want to find],“tablename”,“ColToSearchIn”, [ColToReturn])

And it seems odd to me that in-column and return-column do not have the same syntax, which I guess is why I keep adding brackets, and often refer back to the help docs to and end up fixing the “in-column” syntax.

Steve

How could the help doc be changed to help you?

Mike_A

Lol. Not sure I would change it just for me as others may not think like me

But… I am definitely not a good help text writer and I am sure examples from other applications can be leveraged. maybe it’s just adding better clarity what is meant by each option, and the acceptable data types.

example:
current help
LOOKUP( find-value , in-dataset , in-column , return-column )

alternative

LOOKUP( value-to-find , dataset-source-name , in-which-column , column-to-return )
variable options:
value-to-find | [col name] or “text”
dataset source name | “text”
in-which-column | “text”
column-to-return | [col name]

Lynn

Maybe “column-to return” could be 'value -to-return" You have column names and column values.

Mike_A

Well… since it’s expected to be a column name, I guess it’s really “col-to-return-value-from” … but that’s way long!

Steve

@Jeremy_F’s solution is functionally equivalent, as a deref is essentially a LOOKUP(), but requires a specific table structure that might not exist or may be undesirable.

bouhme

Thank you and all others for your help. I try it and the expression assistand marked it as ok. But if i try it in the app nothing is copy to the target column.

Must both tables have the column AuftragsNr ? Because the sourcetable haven´t.

Steve

The columns named by the third and fourth arguments must exist in the table named by the second argument.

Steve

Do want to copy between two tables, or between two rows within a single table?

bouhme

I want to copy between two tables.
And yes it work. Sorry ii´s my mistake. You write it´s only work if I creat a new row. First I try it with an existed row.

Is there an nother way I can do if the row is exist? Sometimes I have to fill out the longtext field later in the existing row. Perhaps by an action button?