Duplicate reporting and formatting

[Neal_MF_Harper]

Hello All!

I have a huge list of merchandise with unique ID column [Merch ID], one of the fields is a “style” number [Style #], and like a UPC, it can be the same for several of our merchandise items, Merch ID representing the physical item, Style # representing they type of product, basically.

I’m trying to figure out a way to associate the merch items with the same [Style #], and formatting the [Merch ID] in a way team members will see at a glance that there are several instances of the same style number.

It would be great to have some sort of “related to” feature so when you look at the details of a merch item it shows the other items in that style group…

[Lucas_Cuypers]

HI,

are you looking for format rules ?
so basically when an item is part of style A it will be displayed in red , Style B in green etc…

is this what you are looking for…

[Neal_MF_Harper]

I have tens of thousands of merch items, every once in a while we have multiple items with the same Style number, so need to format for “style number is not unique”… a bonus would be some relation to quickly see the other items with the same style number.

[Lucas_Cuypers]

You will probably need some one more experienced with code for this.
one way you can try to identify it is to make an extra deck view and let is group on this style number.
select group aggregate on count.

now you can go into this view on your app and scroll down, you will see a number when more than one is existing…

when this happens you can click on the group and change / repair it.

maybe this is a way ?

[Neal_MF_Harper]

Thanks for the suggestions, but not feasible for this application. There is no repair or change needed, it is a visibility need. Less than a fraction of a percent of my merch list have duplicate style numbers.

[Steve]

Neal_MF_Harper:

need to format for “style number is not unique”… a bonus would be some relation to quickly see the other items with the same style number.

Lots of opportunity for inefficiency here that would slow your app down unbearably. Consider that very carefully as you work through this problem.

You could create an action of type App: go to another view within this app, with a target formula that uses LINKTOFILTEREDVIEW() to generate a filtered view of the appropriate related styles, a prominence of Display inline, and choose a column to attach the action to. This will cause the action’s icon to appear next to the column in detail views (as happens with Email or Ref columns) that the user can tap to activate the action. The icon will replace the column’s value in table views (there are workarounds for this). To display the action in deck views, add the action to the view’s action set.

The above always displays the action icon, though, whether there are related styles or not. You can use the action’s Only if this condition is true property to display it selectively. Hopefully you have some easy way to determine whether an item has related styles. Keep in mind that this Only if this condition is true property will be evaluated once for every row during sync, so you want a very lightweight expression if you’re dealing with tens of thousands of rows. You do not want to use FILTER() or SELECT() here.

[Steve]

Steve:

Keep in mind that this Only if this condition is true property will be evaluated once for every row during sync, so you want a very lightweight expression

For instance, maybe each item has a Yes/No column that indicates it has related styles or not. Then no computation is needed at all, just a reference to that column value.

[Neal_MF_Harper]

Thanks for the great suggestions Steve, Link to filtered view worked great.

Would it be possible to take this same filtered view approach to create an inline table for my detail view?

Thanks for your input!!
Neal

[Steve]

The inline view is trickier, as the data set it uses isn’t generated on the fly like that presented through a filtered view.

For the inline view, you’ll need a normal or virtual column to contain the list of related styles. Each has its advantages and disadvantages.

A normal (non-virtual) column is likely to perform faster than a virtual column during routine use because no computations occur at sync time. Instead, the related-styles lists are generated only when rows of the table are saved from a form or when prompted by a workflow.

A virtual column occurs in and is computed for every row, which means performance can easily become an issue, especially with tens of thousands of rows. The upside is the lists will always be as up-to-date as possible and require no editing of related rows or workflows.

It occurs to me that, if there is already a Ref relationship between the styles, the lists you need may already exist thanks to AppSheet’s auto-generated Related columns.

[Neal_MF_Harper]

I’m down with giving the virtual column a try to see how much it bogs down. Would FILTER() be the way to go for the virtual column formula?

You’re awesome Steve!
Neal

[Steve · Accepted solution]

Yep, use FILTER(). If you have that Yes/No column I suggested earlier that indicates whether the row can even have related styles, you’d do well to use it as well to prevent the FILTER() from running for rows you know will have nothing related:

IFS(
  [Has related styles?],
  FILTER(...)
)

Try both with and without to see what the performance difference is.

[Neal_MF_Harper]

FILTER(Merch, [Style #]=[_THISROW].[Style #])

IT’s ALIVE!!!

You are the man.

[Neal_MF_Harper]

Do you know any reason the inline would populate in the emulator but not elsewhere? I tried refreshing and reloading the browser view on two machines… opened my phone and they display correctly, strange behavior.

[Steve]

I do not. Using the same login on each device? Security or slice filters? User settings? Live app vs. development app?