Ordering dynamically generated props.columns

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?

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

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

image

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 ?

1 Like

Isn't this the same question as before? Strange Script behavior reordering columns.

1 Like

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

Show your SQL and your script transform.

1 Like

SQL:

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:

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,

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.

Current result:

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

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

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.

2 Likes

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

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.

1 Like

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