Re: Question - non-consistent result in referenced...

Aurelien · 02-22-2021 07:12 AM

Hi there

I’m experiencing something unusual, I’m pretty sure my formula used to work, but…maybe not.

I have various tables:

File
Detail_File (children of File)
Businesses

Each Detail_File has 1 “related business” column.
Each business owns properties, and among these: business managers1 and business manager2 in particular (let’s call them BM1 and BM2)

My need is to have a list of ref at the File level, referencing all business managers from the whole related detail_file items.

My formula is:

[Related key_Managers]=
[Related key_Business][key_BM1]+[Related key_Business][key_BM2]

[Related key_Managers] is of type List of Ref, referring to employees table.
[Related key_Business] is of type List of Ref, referring to businesses table.

…No manager comes back to the File level, although [Related key_Business] displays a list of businesses as expected.

In addition, when I look to the table of businesses, the BM1 and BM2 are well reported (as well as all the other businesses properties).

Any clue on what may have gone wrong ?

Many thanks in advance

Steve

I haven’t tinkered with using = with lists recently, but my recollection is that lists are compared as Text values, meaning each list is converted to Text first, then those values are compared. You’ll run into problems if the lists are in different orders and/or contain duplicates. So, ideally, you’d do this:

SORT(list1 - LIST("")) = SORT(list2 - LIST(""))

or, if your lists may legitimately have blank elements (both of the following are equivalent):

    SORT(list1 - LIST()) = SORT(list2 - LIST())

    SORT(UNIQUE(list1)) = SORT(UNIQUE(list2))

Assuming your goal is the determine whether the two lists contain the exact same distinct items.

Aurelien

Thanks @Steve,

I just realize I explained poorly my idea.
Just an old habit to describe, like when I was a young student: A=2+3.
I will give this habit away.

The purpose was not to make a comparison here.
Expressed differently, for the column [Related key_Managers] which is supposed to collect all referenced manager owning businesses, the formula I use is:

[Related key_Business][key_BM1]+[Related key_Business][key_BM2]

Is it clearer this way ?
So, expressed differently again, I want to get a list summarizing all related managers to my file, according to the related details in the file.

Let me know if this is more accurate.
And thanks again for your help !

Steve

The way you’re doing it is appropriate. You might want to wrap the result in UNIQUE() to avoid possible duplicates.

MultiTech

I might also add

- LIST("")
to remove any blank values that may appear

MultiTech

If I have your stuff correct:

[Related key_Business][key_BM1] would be??? A reference to the employees table?

[key_BM1] is what in the business table?

Aurelien

@MultiTech_Visions

[Related key_Business][key_BM1] would be a list of ref to employees table, indeed.
[key_BM1] is the key-column in Employees table.

I should have written : [related_key_BM1] instead of [key_BM1] actually.
And [related_key_BM1] belong to business table.

Aurelien

Thank you to both of you.
I will improve my formula to avoid any blank or duplicate items, thanks for these tips.

In the meantime, I think something else keeps going wrong : if may formula is right indeed, then there is no way I’m having conflicting results.
here is a video trying to explain my misunderstanding.
(please excuse my accent )

Steve

Please post screenshots of the Related Detail column configuration and its complete App formula expression.

Aurelien

Good to ask, there is a step in between the file and the detail.
I did not mention it as I didn’t think that was useful.

When you talk about column configuration : are you thinking about that: key, label, show, editable, etc…?

Related Detail App Formula:

SPLIT(
     CONCATENATE(
          [Related Detail_Fiches][Related Detail_Jours]), 
    ","

)

Related Detail_Fiches App Formula:

REF_ROWS("Detail_Fiche", "Related_Key_FH")

Related Detail_Jours App Formula:

REF_ROWS("Detail_Jour", "Related_Key_Detail_FH")

Steve

No. I mean the column configuration screen:

SPLIT() and CONCATENATE() are likely the problem. Try removing them both:

[Related Detail_Fiches][Related Detail_Jours]

Aurelien

Here is the configuration.

I just tried removing the split/concatenate, but got a list of list of ref, and other formulas depending on it made an error to appear.
As it’s late here, I will give it a rest till tomorrow in the morning.

I’ll keep you updated on my progress.

Aurelien

Hi @Steve ,

I’ve been working on the whole data set, cleaning display to better understand what happens.
Now I realize there is a difference into data process, cannot explain why.

Here is what I found:

App Formula for Related Technicien

UNIQUE( 
      [Related Chantiers][Barcode Technicien Responsable1]
      +[Related Chantiers][Barcode Technicien Responsable2] 
) 
-LIST("")

Result:

Any clue ?

Steve

What are the column types of the Barcode Technicien Responsable1 and Barcode Technicien Responsable2 columns of the Chantiers table?

Aurelien

Type Ref, source Table : Employees
Both of them.

Steve

Note that in order to update the computed value stored in the spreadsheet, you must open the row in a form view and Save it (even without making any changes). Have you tried that?

Aurelien

I’m not sure of understanding what you mean.
In the app, some workflow will write datas, and other datas will be written depending on form saved.
In the capture I showed, nothing was written by other mean than by the app.
Are you saying that, in some case such as “quick edit” (even if, at this stage, this did not happen), there might be error at updating values ?
In addition, nothing in computed in the sheet : only in the app.

Do you think I should engage support on my app ?

Steve

That might be easier. The problem might be readily evident to someone there who can look at your app configuration.

Aurelien

OK, let’s do it then.

Thank you very much anyway for the whole time you spent on my case !

Question - non-consistent result in referenced formula