Noob question: "sum" from list field to master form

segelfreak

Dears,

I'm afraid my coding skills have aged more than I was hoping, when attempting to create a little app here for internal collection of travel CO2 footprint emissions.
Briefly: The task is to create an app which allows to store each travel of an employee (employee->contact->name) and within that, collect 1..n "transport events", such as flights, rental cars, etc., for which we calculate the CO2 emissions. All single emissions should then be aggregated into the travel data set as "total travel emissions".
(Scope 3.6 emission reporting)

Simple structure "in a nutshell", to begin with.

I have succesfully tried to use a list field and to aggregate the sum. As such, the total emissions per travel are correctly shown, but only within the list field.
Now, I could probably write the aggreate sum into a field in the travel table, but that would be a violation to normalisation of the sql data and cause (unnecessary?) redundancy.

Is there a simple way to "migrate" the result of an aggregated list field to the "master" form, maybe into a variable?

The difficulty starts when building the relation for the travel data, where I have to include the employee table and contact table, in order to show properly the common name of the employee. If I also include a relation to the transport events, I naturally get an error (or warning), which makes totally sense.
But when I skip that relation, I can also not define a local variable and use a frontend function, as the necessary data (aggregate sum from the list field) will not be available in the main form.

Maybe I'm just missing a "easy" trick here?

segelfreak

ok, seems I found a solution:

created a collection, aggregating all rows and provide the sum over each travel
created a variable
created a frontend function.

const travelId = app.get_record_id(0);

const emissions = app.collection_read({travel_carbon_footprint.emissions},[{column:0},{column:1}]);

// Zeile finden
const row = emissions.find(r => String(r[0]) === String(travelId));
const value = row ? row[1] : 0;

// In Value Store speichern
const key = `emissions_travel_${travelId}`;
app.value_store_set(key, value);

// Aus dem Value Store lesen (optional)
const storedValue = app.value_store_get(key);

app.set_variable({travel_carbon_footprint.travel_detail.total_emissions}, storedValue);

Now it is showing the sum in the form (as readonly field)

gabriel

segelfreak
Thank you for sharing your solution :)

It makes sense and address your issue as far as I can see. Our forms do not currently have options to collect multi-record data to do aggregations. We could introduce specific fields for this in the future - right now, I would have probably used a backend function to fetch this data live, though a collection works just as well. For presentation on the form, variables are definitely the best choice at the moment.

Btw. I see that you are using the value store - just a short note that this feature is deprecated and variables serve the same purpose. If you need to store data outside/between forms, you can use global variables for this.

segelfreak

Thanks for your swift reply and also your advices.
I was able to successfully create the same with a backend function now:

$BODY$
DECLARE
    total_emission numeric;
BEGIN
    -- Berechne CO₂-Summe für die übergebene Reise-ID
    SELECT SUM((jst_travel_carbon_footprint.transport.carbon_footprint))
      INTO total_emission
    FROM {jst_travel_carbon_footprint}.[transport]
    WHERE (jst_travel_carbon_footprint.transport.travel_id) = t_ID;

    -- NULL absichern
    total_emission := COALESCE(total_emission, 0);

     RETURN total_emission;
END;
$BODY$

The corresponding frontend function (in brief) now looks as follows:

const travelId = app.get_record_id(0);

app.call_backend({jst_travel_carbon_footprint.calculate_co2_total}, travelId).then(
    res => {
        app.set_variable({jst_travel_carbon_footprint.travel_detail.total_emissions}, `CO2: ${res} kg` )
    },
    err => {
        console.error('Fehler beim Backend-Aufruf:', err);
        app.dialog_show('Fehler', `Backend-Fehler: ${err}`, ['OK']);
    }
);

gabriel

Either approache has its pros & cons. Cool that you have worked them out both - now you can choose what you prefer :)