# Ordering dynamically generated props.columns

**URL:** <https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [June 3, 2024, 8:44am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271 "2024-06-03T08:44:26Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [June 3, 2024, 8:44am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271/1 "2024-06-03T08:44:26Z")

</div>

Hello!

I got a transfrom script that brings data to a table depending on the date I choose with a date input, One column per month. But the months don´t show in order. Is there a way I can aply a script to the columns so I reorder them chronologically?

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/1/315225f792cf6493d0c321e2203797fb5f7c0345.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/8/68c69158291366ba6af83fd5087fcf3e3907e177.png)

The DateTime Input choose the start date of the year user want to show on the table

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/6/16001d5478da9cba6789347f587040e538ea6e35.png)

I only fixed first 2 columns because those don´t change

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/c/7c52781a98514e9baf37c4b80fefecbc6f3724aa.png)

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [June 3, 2024, 8:50am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271/2 "2024-06-03T08:50:12Z")

</div>

You can sort the list wherever you're generating it.  
Note: I'd probably try to use a binding instead of a doing this in something else's transform. It makes it easier to see what's dynamic and what's not, and a few other reasons that I won't get into for now.

Can you show us the script and where it is ?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 3, 2024, 8:51am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271/3 "2024-06-03T08:51:23Z")

</div>

Isn't this the same question as before? [Strange Script behavior reordering columns](https://forum.inductiveautomation.com/t/strange-script-behavior-reordering-columns/91193).

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [June 3, 2024, 8:52am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271/4 "2024-06-03T08:52:30Z")

</div>

Yes, it is related but now I´m asking if there is something I can do after to reorder the columns within the props.columns

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 3, 2024, 8:57am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271/5 "2024-06-03T08:57:23Z")

</div>

Show your SQL and your script transform.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [June 3, 2024, 9:02am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271/6 "2024-06-03T09:02:39Z")

</div>

SQL:

```python
SELECT 
    AP.PARENTASSET || '-' || WO.BUSINESSUNIT AS BU,
    WO.WORKORDERNAME,
    TO_CHAR(WO.WORKORDERDATE, 'MM') || '^' || TO_CHAR(WO.WORKORDERDATE, 'YYYY') AS LOC,
    CASE 
        WHEN SUM(
            CASE 
                WHEN (NOT WOOP.POSITION = 1 AND WOOP.OPERATIONSTATE IS NULL OR NOT WOOP.OPERATIONSTATE = 'OK') 
                THEN 1 
                ELSE 0 
            END
        ) > 0 
        THEN 1 
        ELSE 0 
    END AS STATES
FROM 
    WORKORDER WO
JOIN 
    WORKORDEROPERATION WOOP ON WOOP.WORKORDER = WO.WORKORDER AND WOOP.COMPANY = WO.COMPANY
JOIN 
    OPERATION OP ON OP.OPERATION = WOOP.OPERATION AND WO.COMPANY = OP.COMPANY
JOIN 
    ASSETPARENTASSET AP ON WO.COMPANY = AP.COMPANY AND WO.ASSET = AP.ASSET
JOIN 
    ASSET A2 ON AP.COMPANY = A2.COMPANY AND AP.PARENTASSET = A2.ASSET
WHERE 
    WO.COMPANY = 'APPLE'
    AND A2.COMPANYLEVEL = 2	
    AND WO.WORKPROCEDURE = 'SIS10'
    AND WO.WORKORDERDATE BETWEEN :StartDate AND :EndDate
GROUP BY 
    WO.WORKORDERDATE, 
    AP.PARENTASSET || '-' || WO.BUSINESSUNIT, 
    WO.WORKORDERNAME	
ORDER BY 
    1

```

Script transform 1:

```python
def transform(self, value, quality, timestamp):

    if quality.isGood() and value is not None:
       
        py_data = system.dataset.toPyDataSet(value)
        
       
        month_year_set = set(row['LOC'] for row in py_data)
        month_year_list = sorted(list(month_year_set))  

      
        columns = ['Sede', 'WORKORDERNAME'] + month_year_list
        data = []

     WORKORDERNAME pair
        data_dict = {}
        for row in py_data:
            sede_workorder = (row['BU'], row['WORKORDERNAME'])
            if sede_workorder not in data_dict:
                data_dict[sede_workorder] = {month: None for month in month_year_list}
            data_dict[sede_workorder][row['LOC']] = row['STATES']
        

        for (sede, workorder), month_data in data_dict.items():
            new_row = [sede, workorder] + [month_data[month] for month in month_year_list]
            data.append(new_row)
        
    
        new_dataset = system.dataset.toDataSet(columns, data)
        return new_dataset
    else:
       
        return value

```

Script transform 2 for styling the columns,

```python
def transform(self, value, quality, timestamp):

    if quality.isGood() and value is not None:
        output = []

        py_data = system.dataset.toPyDataSet(value)

        for row in py_data:
            newRow = {}
            for col in py_data.getColumnNames():
                cell_value = row[col]

                if cell_value == 1:
                    bgColor = "#FF555588"
                    fontColor = "#ffffff" 
                    display_value = ""
                elif cell_value == 0:
                    bgColor = "#50BE8788"
                    fontColor = "#FFFFFF"
                    display_value = ""
                else:
                    bgColor = ""
                    fontColor = ""
                    display_value = cell_value 

              
                newRow[col] = {
                    "value": display_value,
                    "style": {
                        "backgroundColor": bgColor if bgColor else "transparent",
                        "color": fontColor,
                        "fontWeight": "bold" if bgColor else "normal", 
                        "textAlign": "center",
                        "borderLeft": "1px solid #FFFFFF" if bgColor else "none",  
                        "borderRight": "1px solid #FFFFFF" if bgColor else "none"  
                    }
                }

            output.append(newRow)

        return output
    else:
        return value

```

When I aply script transfrom 2 to style the cells red and green the order change.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/8/d8efadb5485e2d81d514edeeef3502f7e556c079.png)

Current result:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/2/6292e92ebafe983f3bdf925bad8651e79149b0b4.png)

the original data from the query comes with all months in one column

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/a/1aeaf7b6b745e8aa97539099e8b7cc4fee2c6ccc.png)

maybe I should find another way to aply the style to the cell

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [June 3, 2024, 9:09am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271/7 "2024-06-03T09:09:37Z")

</div>

I don't have time to go over the whole thing, but here's what I'd do:

1. make sure the query returns the data in the order you want.
2. make a custom prop and bind it to that query
3. bind the table's `props.data` to that custom prop and add a transform to add the styling you want
4. bind `props.columns` to the custom prop and iterate over your dataset to build the columns list. Datasets, unlike dicts, are ordered. If you build your list from the dataset, the order will be preserved

if you can't sort the data in the query, then do it in the column's script transform using python's `sorted()` or `.sort()`

edit: I missed the fact that you're reprocessing the query's results to format the data in the proper shape... The steps above might not be 100% applicable.  
But the general concept still applies: Build your data in a custom prop, then bind `props.data` to it add styling there, then bind `props.columns` to it as well and sort it there if needed.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [June 3, 2024, 9:35am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271/8 "2024-06-03T09:35:35Z")

</div>

If your columns are not arriving in the right order then try this:

```python
SELECT 
    WO.WORKORDERDATE,
    AP.PARENTASSET || '-' || WO.BUSINESSUNIT AS BU,
    WO.WORKORDERNAME,
    TO_CHAR(WO.WORKORDERDATE, 'MM') || '^' || TO_CHAR(WO.WORKORDERDATE, 'YYYY') AS LOC,
    CASE ...
FROM 
    WORKORDER WO
JOIN ...
JOIN ...
JOIN ...
JOIN ...
WHERE ...
GROUP BY ...
ORDER BY WO.WORKORDERDATE

```

That will give you an extra column, WORKORDERDATE, and the data can be sorted on that. You can ignore the column in your scripts.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [June 3, 2024, 9:44am UTC](https://forum.inductiveautomation.com/t/ordering-dynamically-generated-props-columns/91271/9 "2024-06-03T09:44:09Z")

</div>

> [@Transistor](#):
>
> `ORDER BY WO.WORKORDERDATE`

Thanks Transistor, the order after the query and first transform script is fine,

the problem surges after the 2nd transform script to style the cells..

aniway I aplied the changes to the query too.

I will try to aply this style some other way. I will try use the @pascal.fragnoud solution and aply it to a preloaded custom.data dataset

Thanks for your help