Trying to remove duplicate for a <<START>> .... <<END>> Loop?

AppSheet AppSheet Q&A
1

I am trying to removed duplicates for a Loop so I can Loop inside the Loop!

<<START: UNIQUE(ORDERBY(FILTER(Items, AND([Building ID] = [_THISROW].[Building ID], [Item] <> “None)), [Location], FALSE))>>

Another Loop below

<<START: ORDERBY(FILTER(Items, AND([Building ID] = [_THISROW].[Building ID], [Location] = [_THISROW-1].[Location], [Item] <> “None”)), [Location], FALSE)>>

<

<>

Without the inner loop it works.

Once I put the inner loop in it duplicates as if ignoring the UNIQUE() function

1 Like
2

Please show screenshots of your tables and an example of the desired results.

1 Like
3

Its a very complicated system.

But here is an overview of the current issue

Building Table has an Items Table

Each Item will have a Location Field

For each Location in a Building I want to report on all the items in that Location. (Location Loop 1)

Loop 2 shows all the items for that Location

Then another section in the report for the other locations.

I would have thought 2 START / END loops would have worked by using UNIQUE(). But the UNIUQUE does not work in a START statement.

4

Putting UNIQUE() around FILTER() has no effect: FILTER() returns row keys, which are already unique.

3 Likes
5

Adding to @Steve 's comments, what do you need exactly? Are you trying to create groups of data? This is not a difficult thing.

Solved: How to separate rows of a table based on unique va… - Google Cloud Community

2 Likes
6

Try this as the first loop:

<<Start:
  ORDERBY(
    FILTER(
      "Items",
      AND(
        ([Building ID] = [_THISROW].[Building ID]),
        ([Item] <> “None"),
        (
          [_ROWNUMBER]
          = MIN(
            SELECT(
              Items[_ROWNUMBER],
              AND(
                ([Building ID] = [_THISROW-1].[Building ID]),
                ([Item] <> “None")
              )
            )
          )
        )
      )
    ),
    [Location],
      FALSE
  )
>>

I’ve indented it for clarity, but you must remove the indenting to use it.

3 Likes
7

Thanks Steve. I’ll give it a go.

1 Like
8

Afaik, indenting inside any expression shouldn’t affect it’s usage, neither on templates or the expression assistant

2 Likes
9

I’ve been thinking the same, but I haven’t tried indentation inside a template.

1 Like
10

It works on my side, although I use .html templates instead of GDocs/MSWord.

On those WYSIWYG editors any formula messes with the layout a lot, but I guess after the expressions are replaced with actual values it should look fine

2 Likes
11

Unfortunately this returns he first Location and nothing after.

The following my list of unique locations

UNIQUE(SELECT(ITEMS[Location],
AND
(
[Building ID] = [_THISROW].[Building ID],
[Survey Section] <> “EXCLUDED AREAS”
)
))

In normal programing I would pick off each locations in the list and process it in a loop.

But the START need a list of IDs

Is there any way to use the list of locations in the above SELECT to filter in the Inner START loop?

1 Like
12

I and others have had problems, but it may only be a problem in certain contexts.

1 Like
13

Sure but we need to understand your tables schema before we can suggest anything meaningful

1 Like
14

I guess then it would be OK in an html template since spaces are ignored anyway, but would be problematic otherwise.

2 Likes
15

Whoops! I left something out. Try this instead:

<<Start:
  ORDERBY(
    FILTER(
      "Items",
      AND(
        ([Building ID] = [_THISROW].[Building ID]),
        ([Item] <> “None"),
        (
          [_ROWNUMBER]
          = MIN(
            SELECT(
              Items[_ROWNUMBER],
              AND(
                ([Building ID] = [_THISROW-1].[Building ID]),
                ([Item] <> “None"),
                ([Location] = [_THISROW-1].[Location])
              )
            )
          )
        )
      )
    ),
    [Location],
      FALSE
  )
>>
3 Likes
16

Absolutely brilliant Steve.

You are a :glowing_star: :glowing_star: :glowing_star: :glowing_star: :glowing_star: STAR :glowing_star: :glowing_star: :glowing_star: :glowing_star: :glowing_star:

Works a treat.

I understand THISROW-1 in the context of nested START statements. And THISROW in SELECT statements to focus on the current record.

But I cant quite get my head a round the THISROW-1 in the SELECT?

Is it referring to the scope OUTSIDE the SELECT statement?

2 Likes
17

Back-references within nested queries ([_THISROW-n])

2 Likes