Table

Using the Handsontable library

For this panel content, the panel frame url_name must be frame_table, and it must have a url_action_name for the Automail URL controller to find the panel content.

Below is an example configuration of a panel using the Table module:

{
    "title": "Default Lead-Time",
    "width": 12,
    "full_row": True,
    "url_name": "frame_table",
    "url_action_name": "table_defaultleadtimetracker",
    "content": {
        "view": api_views.Table,
        "view_params": {
            "model": custom_models.DefaultLeadTimeTracker,
            "order_by": "id",
            "init_max_rows_threshold": 20000,
            "table_settings": {
                "options": {
                    "hide_id_column": True,
                },
                "permissions": {
                    "read": {
                        "allowed": True,
                        "allowed_user_groups": [],
                        "allowed_users": [],
                    },
                    "update": {
                        "allowed": False,
                        "allowed_user_groups": [],
                        "allowed_users": [],
                    },
                    "insert": {
                        "allowed": False,
                        "allowed_user_groups": [],
                        "allowed_users": [],
                    },
                    "delete": {
                        "allowed": False,
                        "allowed_user_groups": [],
                        "allowed_users": [],
                    },
                },
            },
            "field_definitions": {
                "supplier__name": {
                    "source_include_filters": {
                        "is_t2": True
                    }
                },
                "season__search_reference_suggestion_text": {},
                "web_number": {},
                "update_timestamp": {"read_only": True},
                "user_id": {"read_only": True},
            },
            "include_filters_extra": {
                "report_category": "Delivery Report",
            },
        },
    },
}

The view_params are:

• model the model this table is based on

• order_by the model field (str) or fields (list) to order the table by when loading (by default ascending alphanumeric order, for descending order use the prefix "-" (e.g. "-id"))

• is_empty sets whether the table should be empty by default until the user clicks the Search button (must have a selectpickers section on top of the page)

• init_max_rows_threshold the maximum number of rows that will be displayed without the user clicking the Search button

• table_settings the table-level configuration

Create an uploadfile__.py to upload the table in database

try:
    from _init import *
except ImportError:
    from configurations.workflow_env import *
from polygon.apps.app_flows.controller import process_uploadfiles


def process_uploadfiles_monitor_coc():
    process_uploadfiles(
        **{
            "model": custom_models.Monitor,
            "table_settings": {
                "options": {
                    "hide_id_column": True,
                    "fixed_write_fields": {
                        "survey": "Chain of Custody Management",
                    },
                },
                "uploading_config": {
                    "extract": {
                        "sheet_name": 0,
                    },
                    "load": {
                        "do_bulk_insert": True,
                        "method": {
                            "function": "upsert",
                            "params": {
                                "unique_together": True,
                                "key_fields": [
                                    "season",
                                    "supplier_id",
                                    "surveygroup_id",
                                ],
                            },
                        },
                    },
                },
            },
            "field_definitions": {
                "season": {"allow_empty": False},
                "surveygroup__name": {
                    "allow_empty": False,
                    "source_include_filters": {"survey": "Chain of Custody Management"},
                },
                "supplier__name": {
                    "allow_empty": False,
                    "threshold_level": "strict",
                    # "label_alternatives": ["Select Supplier"],
                },
                "supplier__suppliergroup__name": {
                    "threshold_level": "strict",
                },
                "survey_contact_email": {
                    "allow_empty": False,
                    "internal_type": "EmailField",
                    "threshold_level": "strict",
                },
            },
        }
    )


if __name__ == "__main__":
    write_to_file(log_file_path, "process_uploadfiles_monitor_coc")
    monitoring(process_uploadfiles_monitor_coc)
    write_to_file(log_file_path, "completed")

For each column name in "field_definition", you can set up additional settings for the table

"field_definitions": {
    "survey": {
        "source": custom_models.KEY_MATERIAL_SURVEYS,
    },
    "question": {
        "width": 300,
        "validations": [
            {
                "name": "custom",
                "params": {
                    "function": custom_functions.validate_question_exist,
                    "message": "The question(s) are not valid for the corresponding survey. <br>",
                },
            }
        ],
    },
    "answer": {
        "width": 300,
    },
    "survey_contact_email": {
        "transformations": [
            "lower",
            {
                "name": "sort_alphabetical_split_by_separator",
                "params": {
                    "separator": ",",
                },
            },
        ],
        "validations": [
            {
                "name": "custom",
                "params": {
                    "function": custom_functions.validate_contact_email,
                    "message": "Only valid email addresses allowed for",
                },
            },
        ],
    },
    "update_timestamp": {"read_only": True},
    "user_id": {"read_only": True},
},

• source the value in each row for the column should be in the field of the list you provided

• width set up the initial width for the column

• validations will validate the input value in the column and provide the message showing when the validation fails. You can set up the custom function in function.py like this

def validate_question_exist(df, filtered_df):
    def apply_validation(row):
        questions = list(
            custom_models.MonitorQuestionAnswer.objects.filter(
                question_category=row["survey"]
            )
            .values_list("question", flat=True)
            .distinct()
        )
        return row["question"] in questions

    df["is_valid_flag"] = filtered_df.apply(apply_validation, args=(), axis=1)
    return df

• transformations can transform the initial input value in the table to a specific value based on the custom function or a set-up function.

Process_upload_file function parameters are:

• model the model this table is based on

• table_settings can set up the configurations in the table

• field_definitions determine the column and its constraints, and you can also provide the column label alternatives to catch the column in the table.

After dropping the Excel file in the upload box, you can run the uploadfile__.py to load the table in the database.

You can also set the uploadfile__.py as a Dag. Check the dag part

Dags