I already posted yesterday about a problem wi...

references
(Adrian Enz) #1

I already posted yesterday about a problem with dereferences. ([THISROW BEFORE] and that I don’t really grasp how it really works.)

Now i stubled upon another problem.

I wanted to create a more complex formula which compares entries over different referenced tables. But I don’t get the dereferences to work.

I make it as simple as that: [CON-BIP M01: Vorbereitung abgeschlossen].[Start Datum]

The first one is the column that is the reference. The second one is a column name from the referenced table. In the dereferences video from appsheet they do it the same way and it works. In my case I get an error.

What am I doing wrong?

(Steven Coile) #2

What error are you getting?

(Adrian Enz) #3

No explanation, just a red X.

(Adrian Enz) #4

I shortened the ref column. Still the same error.

(Adrian Enz) #5

+Steve Coile

(Steven Coile) #6

@Adrian_Enz What are the types of the 2 columns?

(Adrian Enz) #7

The first one is List Type as it is a reference.

The second one is an Enum Type.

(Steven Coile) #8

@Adrian_Enz I’m confused about your statement. A List is not inherently a reference, nor is a Ref inherently a list. A List can be a list of Ref, but a Ref cannot point to a list. So I need some clarification.

Could you provide a screenshot of the column configuration editing screen for the list/ref column?

(Adrian Enz) #9

+Steve Coile

For explanation what I’m trying to achieve: I’ve got a Database with “Customers”.

Then I got separate Databases for Milestones we need to achieve.

I want to have a virtual column in the customer table that looks up the value from every Milestone if it is started (column “Start”)

(Steven Coile) #10

@Adrian_Enz Okay, so the issue is because the column is a list of references. Your list of references is valid, but you cannot de-reference it the way you are.

A simple de-reference involves a single Ref-type value (typically contained as a single value, not in a list) in a column, followed by a period, then the identifier of the column in the row referenced. However, this approach does not work if the Ref-type value is in a list, as you’re trying to do.

In order to de-reference a list, omit the period between the column identifiers:

[CONBIPM01][Start]

Instead of:

[CONBIPM01].[Start]

Note that the result will itself be a List of [Start] values.

(Adrian Enz) #11

+Steve Coile Thank you very much, know I understand how it works. I also tried it with the funciton Any to get the value and not a list of values, as I only have one row per referenced list.

(Steven Coile) #12

@Adrian_Enz Very odd! Can you post a screenshot?

(Adrian Enz) #13
(Steven Coile) #14

@Adrian_Enz Ah. You’re using the IFS() function, which should always have an even number of arguments. Yours has 3, an odd number. Either remove the last argument ("") or change IFS() to IF().

(Adrian Enz) #15

Yes I now that it is wrong, just typed it in to show you the error.

The same error appears when I only have the dereferenc with a point inbetween to get the value from the cell.

(Steven Coile) #16

@Adrian_Enz Can you shorten that column name (temporarily)? Doing so might help us see what’s not shown.

(Adrian Enz) #17

You mean the column name of the reference? (CON-BIP…)

(Steven Coile) #18

@Adrian_Enz yep.

(Adrian Enz) #19

Okey, I’ll do that later as I’m out of the office at the moment.

But thanks for your help. I’ll get back later.