Can anyone help me to create a security cross

[Anonymous]

I'm creating a "security cross" that has inside a button with each day of the month
i want that each button changes it fill color depending on the event that happens at the end of that day
 
I use this formula

If(Dropdown3.SelectedText.Value="Acto Inseguro",RGBA(141, 198, 63, 1),RGBA(166,166,166,1))

If the event is equal to
Unsafe Act, Unsafe Condition or NM the button fills Green
If it is
First Aid, SNM fills in yellow
and in a fatal event it will fill Red

[GarethPrisk]

You would probably want to leverage a Switch function.

Switch(
    Dropdown3.SelectedText.Value, // Control/Value to Switch On
    "Acto Inseguro", // Value Option #1
    RGBA(141, 198, 63, 1), // Match # 1
    "Yellow Value", // Value Option #2
    RGBA(255, 255, 152, 1), // Match #1
    RGBA(166,166,166,1) // Default Color
)

I didn't add your specifics, because I think they may be translated differently. This at least will allow you to set the RGBA value for multiple branches. You could technically do nested If statements, but that can get tricky. This approach is nice because, even though you're using the same color for different value matches, it's very easy to read/track what value goes with what, and you get a default.

[Anonymous]

That work fine but how can i do this next step

Every day the EHS depart,et add a new record to the table with all the information of the accidents etc , So, i need a formula that changes the color of the button depending on the date and on the information

Ass ypu can see in the image i have 30 buttons on for each day of the month I need that the button changes it color and maintan
an example
on February 14 no accidents happen (button fills Green)
February 15 we have an small incident (button fills green)
february 16 we have a minior accident (button fills yellow)
February 17 a fatal accident happen (button fills red)

and those button's colors need maintan until the month ends or i reset them to its original value

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

Replace any names below in blue italics with those from your app.

• Your EHS department has a list I will call EHSList
• In this list is a ReportDate date matching one of the buttons on your cross.
• Also on this list is a text field reporting accidents called Incident.

On each button, you need to find the record that matches the button and set the Fill property accordingly.

You will also need to Collect the list as Date calculations are not a delegable query with SharePoint (I assume this is your data source), so first thing you need on the OnVisible property of the screen is

ClearCollect(colEHS,EHSList)

The list needs to be under 2000 records for this to work, however I assume yours will be well under if one line per day.

So you will have 31 of the formulas below (one in each of the button Fill properties), just change the day number (three places) to match the button number. 

If(
   LookUp(
      ColEHS,
      Day(ReportDate) = 1 && Month(ReportDate) = Month(Today()),
      Incident
   ) = "Fatal Event",
   Red,
   LookUp(
      ColEHS,
      Day(ReportDate) = 1 && Month(ReportDate) = Month(Today()),
      Incident
   ) = "First Aid",
   Yellow,
   LookUp(
      ColEHS,
      Day(ReportDate) = 1 && Month(ReportDate) = Month(Today()),
      Incident
   ) = "SNM",
    Yellow,
    Green
)

The cross will also automatically adjust on the first of the next month. If you did not want this, put in a text box MonthNo for the month number and replace

Month(ReportDate) = Month(Today())
with
Month(ReportDate) = Value(MonthNo.Text)

 Happy to explain further

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[GarethPrisk · Accepted solution]

Thanks @WarrenBelz  for moving this forward.

I wanted to address the elephant in the room on what we are proposing thus far - too many repeated controls. Notably, we are proposing 31 buttons which will all need to be coded individually, and will likely result in a maintenance headache. Imagine if one little aspect changes, then it's as many as 31 buttons to review and update. Ouch!

Enter the humble Gallery control! With some creative thinking, we can replicate this custom layout/behavior, but with significantly less controls, and much easier maintenance.

I have attached a sample app, which I will reference from now on.

First, the design:

• @Anonymous  wants to show data from a month, in a 'cross' format
• The cross is made up of individual days of the month, to represent the total shape
• Months may have 28,29,30, or 31 days, depending on the month/year
• The cross shape will likely have an even number of used spaces, so we need to round up to 32
• Each cell of the cross shape will behave differently, and contextually
• Each cell of the cross shape will reference a single (latest) record from an Events table

The approach

• Use a Gallery to reflect a grid, or matrix
• Determine which cells in the matrix correlate with the cross shape
• Determine which cells in the matrix correlate with days of the month
• Build the Gallery matrix structure
  • Through some drawing in Excel, decided that the cross shape fits well in a 48 cell matrix
  • The corner 4 cells of each corner are not going to be used
  • The rest, starting at top-left, will represent days of the month, sequentially
• Build the Gallery data source, based on the structure and requirements
  • Vertical Blank Gallery
  • Wrap Count = 8
  • Template Padding = 0
  • Template Size = 100
  • Gallery Width = 800
  • Gallery Height = 600
• Add controls to each Gallery cell, to behave based on their specific representation
• Profit

Begin by initializing the Gallery, with the 48 cell structure.

// This step establishes local variables to help build the matrix; you could use global with Set(var,x)
UpdateContext({
// The 8x6 matrix cells
locGalleryMatrixStructure: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48]
// The cells we want to use
,locGalleryMatrixCellsUsed: [3,4,5,6,11,12,13,14,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,35,36,37,38,43,44,45,46] 
})

Then using a ForAll loop, generate a Collection for our actual Gallery, using the mapping we could plot out on the 48 cell matrix.

// Clear Gallery Matrix
Clear(colGalleryMatrix);
// Build Gallery Matrix, based on Structure
ForAll(
    locGalleryMatrixStructure,// The 48 cells of our Matrix, looped from 1-48
    Collect(
        colGalleryMatrix,
        {
            matrixIsUsed: If(
                locGalleryMatrixStructure[@Value] in locGalleryMatrixCellsUsed.Value,
                true,// This cell is being used, set true
                false// This cell is not be used, set false
            ),
            matrixDayOfMonth: If(
                locGalleryMatrixStructure[@Value] in locGalleryMatrixCellsUsed.Value,
                Max(
                    colGalleryMatrix,
                    matrixDayOfMonth
                ) + 1,// Incrementally Set the Day of Month, then Add 1 for each valid cell
                Blank()
            )
        }
    )
)

Create a Month/Year drop-down, to allow easy User filtering.

// Simple Global Variable to give us a Calendar dropdown to let the User pick the Month/Yeah, and for filterin
Set(
    gblCalendarDropDown,
    Table(
        {
            calendarDisplay: "January 2020",
            calendarMonth: 1,
            calendarYear: 2020
        },
        {
            calendarDisplay: "February 2020",
            calendarMonth: 2,
            calendarYear: 2020
        },
        {
            calendarDisplay: "March 2020",
            calendarMonth: 3,
            calendarYear: 2020
        }
    )
)

Generate Event data for this month, and last month, to reference.

// Clears our Events table
Clear(colEvents);
// Generate Events
ForAll(
    locGalleryMatrixStructure,
    // Collect a record for the Current month
    Collect(
        colEvents,
        {
            eventDate: DateTimeValue(
                Text(
                    DateAdd(
                        Date(
                            Year(Now()),
                            Month(Now()),
                            RoundUp(
                                Rand() * 31,// Random Day of this Month
                                0
                            )
                        ),
                        RoundUp(
                            (Rand() * 24),// Random Time
                            0
                        ),
                        Hours
                    ),
                    DateTimeFormat.ShortDateTime
                )
            ),
            // Random Event Type
            eventType: If(
                Rand() > .66,
                "Normal",
                Rand() < .33,
                "Bad",
                "Good"
            )
        }
    );
    Collect(
        colEvents,
        {
            eventDate: DateTimeValue(
                Text(
                    DateAdd(
                        Date(
                            Year(Now()),
                            Month(Now()) - 1, // Last Month
                            RoundUp(
                                Rand() * 31,// Random Day of last Month
                                0
                            )
                        ),
                        RoundUp(
                            (Rand() * 24),// Random Time
                            0
                        ),
                        Hours
                    ),
                    DateTimeFormat.ShortDateTime
                )
            ),
            // Random Event Type
            eventType: If(
                Rand() > .66,
                "Normal",
                Rand() < .33,
                "Bad",
                "Good"
            )
        }
    )
)

Update the Gallery controls, to contextually lookup the filtered Month and cell's day, to find the latest Event and use it's Event Type value to highlight the cell and show the outcome. This is basically the end result.

This has been generalized, but can be refactored, and the principles applied to the cross app (or any cell-based shape needs that have similar requirements). Please let me know if you have any questions, feedback, or criticisms of the approach.

CC: @PowerAddict

[Anonymous]

That work perfectly, i justy add more variables and its working as i expected 

[Anonymous]

this is awesome, you nailed it

[GarethPrisk]

Awesome! Glad to hear. 🙂