Transfer data from one table to another

[LHAdmin]

I have an app with a table called Kunder, where each task has a case number. In all the other tables this case number is used as a ref.
Each time the employee spends time on a case, they make a time record in another table Stempelur. In that table the Case No is a Ref wow, So there are multiple lines with the same Case No. In this table I have created a virtuel column with a formula that automatically adds all the registrations to a sum of hours. This column is called Dag Decimal. I would like to transfer the value of this column to the Kunder table where the Case number is the same.

Can you create a formula or does it have to be done via automation?

I have tried to use Dereferenced, but in 

[Bethel_Vasto]

You should be able to do this with a Virtual Column in the Kunder table, no automation required (my understanding is that the Stempelur table has a case number column too, in that case the formula for the virtual column on the Kunder table should be pretty straightforward)

[LHAdmin]

Thanks. I thought so. Now - the problem is i don't know how that formula should look like. I have tried a Lookup - but it doesn't work.  Is it possible for you to help me ? 

[Bethel_Vasto]

is the kunder table a parent table to the Stempelur table or is it the other way around?

if there is no relationship bethween the two tables,
it would look like this:

SELECT
(Stempelur[Dag Decimal],
[Case number]=[_THISROW].[Case number])

although it should work wether or not a parent/child relationship exists between the two tables.

please take this with a grain of salt as I am not an expert and I always have to double check my syntax and I rely a lot on the formula parser for number of open and closed parentheses.

https://support.google.com/appsheet/answer/10108207?hl=en 

Edit: the select function, used as I did in the example I provided, will return a list (wether or not there is more than one value in that list, it will be recognized as a list). if your virtual column is not of list type you might get an error. In that case you either change the column type to list , or you rewrite you wrap your formula in ANY(), like this:
ANY(
SELECT(
....etc)

)

[LHAdmin]

The formula is valid:

Any(SELECT
(Stempelur[Dag Decimal],
[Case no]=[_THISROW].[Case no]))

But i does not return the value to the Kunder table. Maybe because the column in Stempleur is a virtuel column? Should the column be in my Google sheet for it to work? I mean - not as a virtuel column?

[Bethel_Vasto]

it should work even if the column in the Stempelur table is a virtual column itself.

I am going to do what the experts do and ask you to post screenshots 😄

[LHAdmin]

This is the formula in the table Kunder

This is the formula in table Stempelur that sums up the hours and convert them to a decimal.

As you can see there are data in the column Dag Decimal

But when i check me sheet the cells ar empty. I have tried to ad new data to the table Stempelur to get it to recalculate. But still blank cells in the column tha has the formula 

Any(SELECT
(Stempelur[Dag Decimal],
[Case no]=[_THISROW].[Case no]))

[Bethel_Vasto]

you say when you check the sheet the cells are empty. that's correct, the whole purpose of a virtual column is that the data doesn't get written on the sheet. It's there in the app, but it's virtual and not "literally" written on the sheet.

Maybe I'm missing something?

EDIT:
did you add the virtual column to the column order in the UX for the Kunder table ?

[LHAdmin]

The column where i have the

Any(SELECT
(Stempelur[Dag Decimal],
[Case no]=[_THISROW].[Case no]))

formula in is NOT virtual. That's an actual column.

[Bethel_Vasto]

my suggestion from the beginning was to put this formula in a virtual column, literally the first answer I gave here 😅
if you need the data to be updated to existing rows of the kunder table, the approach has to be completely different

[LHAdmin]

Oh. I need the data in existing row. Because at the end i have a "robot3 / API tha transfer this data to another system.

My god - im so lost 🙂

[Bethel_Vasto]

Then you definitely need an automation!

[LHAdmin]

I was afraid you would say that.

Is there any way you can help me with that? 🙏

[LHAdmin]

I was afraid you would say something lige that.😪

Is there any way you can help me with that?🙏

[Bethel_Vasto]

if you have absolutely no idea how to create an automation, the best way to start would be to read the documentation : Automation

[LHAdmin]

Ok. Thank you so far!