Expressions

Jonathan_S
Participant V

I have a question with the max row fuction.

I have the Following Formula which is working but it is returning the ID of the row, I need it to return the Date Value of the Row, Am I doing somthing wrong?

Thanks,

If(IsNotBlank(MAXROW(“Daily Production Record”, “Date”,And( [Customer] = [_THISROW]. 
[Customer],[Flavor] = [_ThisRow].[Flavor], [Con/Org/GF] = [_ThisRow].[Con/Org/GF],[Waffles per 
 Display] = [_ThisRow].[Waffles per Display], [Waffles per Master] = [_ThisRow].[Waffles per 
 Master]))),CONCATENATE("Last Produced on: " , MAXROW(“Daily Production Record”, “Date”,And( 
 [Customer] = [_THISROW].[Customer],[Flavor] = [_ThisRow].[Flavor], [Con/Org/GF] = [_ThisRow]. 
[Con/Org/GF],[Waffles per Display] = [_ThisRow].[Waffles per Display], [Waffles per Master] = 
 [_ThisRow].[Waffles per Master]))), " " )
Solved Solved
0 5 355
1 ACCEPTED SOLUTION

You would need to add another virtual column to your Types table.

Alternatively, here’s a single expression that does everything:

ANY(
  LIST(
    CONCATENATE(
      "Last Produced on: ",
      LOOKUP(
        MAXROW(
          “Daily Production Record”,
          “Date”,
          AND(
            ([Customer] = [_THISROW].[Customer]),
            ([Flavor] = [_THISROW].[Flavor]),
            ([Con/Org/GF] = [_THISROW].[Con/Org/GF]),
            ([Waffles per Display] = [_THISROW].[Waffles per Display]),
            ([Waffles per Master] = [_THISROW].[Waffles per Master])
          )
        ),
        “Daily Production Record”,
        “row-key”,
        “Date”
      )
    )
  )
  - LIST("Last Produced on: ")
)
  1. MAXROW(...) gets the key column value of the row matching your selection criteria and with the most recent date.

  2. LOOKUP(...) uses the key column value from (1) to get the Date column value from the row it identifies. Replace row-key with the name of the key column of the Daily Production Record table.

  3. CONCATENATE(...) constructs the desired text from the date gathered in (2).

  4. (LIST(...) - LIST("Last Produced on: ")) constructs a one-item list from the result of (3), then removes from that list any items that have the exact value, Last Produced on: . If MAXROW() didn’t find anything, then LOOKUP() won’t find anything, and CONCATENATE() won’t have anything to add to Last Produced on: , so CONCATENATE()'s output will be only that. List subtraction allows us to remove that undesirable result.

  5. ANY(...) gives the first (and only) item from the list resulting from (4). Note that if (4) produced an empty list by removing an undesirable result, ANY() will produce a blank value.





View solution in original post

5 REPLIES 5

bowker678
Participant III

You could try to add another virtual column with the following if the ID is a key?

[Name of column shown above].[Date Value Column]

Steve
Participant V

@bowker678’s suggestion is a good one. It’s also a lot more efficient than your current approach.

The virtual column (let’s call it Last Produced) app formula would be:

MAXROW(
  “Daily Production Record”,
  “Date”,
  AND(
    ([Customer] = [_THISROW].[Customer]),
    ([Flavor] = [_THISROW].[Flavor]),
    ([Con/Org/GF] = [_THISROW].[Con/Org/GF]),
    ([Waffles per Display] = [_THISROW].[Waffles per Display]),
    ([Waffles per Master] = [_THISROW].[Waffles per Master])
  )
)

Then your original expression would reduce to:

IFS(
  ISNOTBLANK([Last Produced]),
  CONCATENATE(
    "Last Produced on: ",
    [Last Produced].[Date]
  )
)

Note that MAXROW() provides the key column value of the row with the highest value in the indicated column; it does not provide the value of the indicated column from that row.

Ok, So my first post has this expression in a virtual column called last produced.

I would need 2 virtual columns to make the above relavant? Or are you saying I need to add a virtual column in my daily production records?

I need this value in my Types Table. This is where my virtual column is in my first post.

You would need to add another virtual column to your Types table.

Alternatively, here’s a single expression that does everything:

ANY(
  LIST(
    CONCATENATE(
      "Last Produced on: ",
      LOOKUP(
        MAXROW(
          “Daily Production Record”,
          “Date”,
          AND(
            ([Customer] = [_THISROW].[Customer]),
            ([Flavor] = [_THISROW].[Flavor]),
            ([Con/Org/GF] = [_THISROW].[Con/Org/GF]),
            ([Waffles per Display] = [_THISROW].[Waffles per Display]),
            ([Waffles per Master] = [_THISROW].[Waffles per Master])
          )
        ),
        “Daily Production Record”,
        “row-key”,
        “Date”
      )
    )
  )
  - LIST("Last Produced on: ")
)
  1. MAXROW(...) gets the key column value of the row matching your selection criteria and with the most recent date.

  2. LOOKUP(...) uses the key column value from (1) to get the Date column value from the row it identifies. Replace row-key with the name of the key column of the Daily Production Record table.

  3. CONCATENATE(...) constructs the desired text from the date gathered in (2).

  4. (LIST(...) - LIST("Last Produced on: ")) constructs a one-item list from the result of (3), then removes from that list any items that have the exact value, Last Produced on: . If MAXROW() didn’t find anything, then LOOKUP() won’t find anything, and CONCATENATE() won’t have anything to add to Last Produced on: , so CONCATENATE()'s output will be only that. List subtraction allows us to remove that undesirable result.

  5. ANY(...) gives the first (and only) item from the list resulting from (4). Note that if (4) produced an empty list by removing an undesirable result, ANY() will produce a blank value.





@Steve Thank your Sir. You are an amazing Asset to Appsheet

Top Labels in this Space