Issue getting data from a rollup formula from a script

[Francois]

I use the cloud version and I have an issue when trying to get data coming from a rollup formula from a local python script.

To reproduce the bug I created a two table database. Below is the full debug base structure

— COMPLETE BASE STRUCTURE WITH ALL BASES AND COLUMNS —
.
Table: Main (ID: 0000)
→ ID (text)
→ Children (link → 0000)
→ Sub (link → p5f8)
→ SubValues (link-formula)
→ Cumul (formula)
→ RollUpTest1 (formula)
→ RollUpTest2 (formula)
→ Value (number)
→ SingleTableCumul (formula)
→ RollUpTest3 (formula)
→ OtherCumul (formula)
.
Table: Sub (ID: p5f8)
→ ID (text)
→ Main (link → 0000)
→ Value (number)

The main table looks like this. The minimum required fields are ID, Children and Cumul. The others are only for debug.

The data in the main table is a tree structure

• M001
  • M002
  • M003
    • M004

Each record get values from the Sub Table (VXXX records). Cumul is for a level the sum of the VXXX values directly related to the record and the sum related to its children.

• Cumul = rollup(“Sub”,“Value”,“sum”) + rollup(“Children”,“Cumul”,“sum”)

It works well (or seems to) but I have issues when trying to read the Cumul values from a script
Below is the first basic script I used

from seatable_api import Base, context
import json
import pandas as pd
server_url = context.server_url
api_token = context.api_token
base = Base(api_token, server_url)
base.auth()
json_data = base.query(‘select * from Main’)
main = pd.DataFrame(json_data)
print(main[[‘ID’,‘Cumul’,]])

This is what I get from the script
ID Cumul
0 M001 None
1 M002 None
2 M003 None
3 M004 None

I get a None value for Cumul (which is not what I have on the screen).

I tried other formulas, even implementing a version where the values come from the same table to try to understand where is the problem. Below is the new full debug table, the tested formulas and the results from the new script.

• RollUpTest1 = rollup(“Sub”,“Value”,“sum”)
• RollUpTest2 = rollup(“Children”,“RollUpTest2”,“sum”)
• SingleTableCumul = {Value} + rollup(“Children”,“SingleTableCumul”,“sum”)
• RollUpTest3 = rollup(“Children”,“Value”,“sum”)
• OtherCumul = {RollUpTest3}+{Value}

ID Cumul RollUpTest1 RollUpTest2
0 M001 None NaN None
1 M002 None 12.0 None
2 M003 None 20.0 None
3 M004 None 15.0 None

ID Value SingleTableCumul RollUpTest3 OtherCumul
0 M001 0 None 32.0 32
1 M002 12 None NaN 12
2 M003 20 None 15.0 35
3 M004 15 None NaN 15

It seems that the problem arise when I used a rollup formula in a “recursive way”.

• RollUpTest2 = rollup(“Children”,“RollUpTest2”,“sum”)

The problem is only when trying to get the values from a script. The only way I found to deal with this is to … copy/paste the data in other columns by hand !

I can provide the full debug database (dtable file) if somebody tells me how to do it !

[bennhatton]

Hi @Francois , and welcome to the SeaTable forum !
You started with a pretty tough question
I recreated your table structure and I confirm that I can’t get any result from the python API once there is a recursive use of links. I hope @cdb, @rdb, @fsa or @daniel.pan can give us more information about that…
For now, I’m unfortunately not really convinced by all the workarounds I tested, but here are my best avenues:

• you can eventually recompute your values directly in your python script
• you can use this approach to avoid the recursive use of functions:
  1. Create a SubValuesSum column in your Main table with the following formula: rollup('Sub','Value','sum') (it’s just the sum of every linked Sub values, as the first part of your Cumul column
  2. Create a another Children column (let’s call it SelfAndChildren) that will contain the node itself and every children, grandchildren and so on, and eventually use a python script to update this column automatically
  3. Once this is done, you can create a new NonRecursiveCumul column with the following formula rollup('SelfAndChildren','SubValuesSum','sum'). You’ll be able to access its value from python.

PS : You will find bellow the script I tested to update the SelfAndChildren column from the Children one. Keep in mind that you’'l have to change the link_id! You can find it by listing the columns with base.list_columns('Main') and look for the link_id (not the key !) of your SelfAndChildren column ):

from seatable_api import Base, context
import json
def add_children(node):
    try:
        family.index(node['_id'])
    except:
        family.append(node['_id'])
    if 'Children' in node :
        for child_link in node['Children']:
            family.append(child_link['row_id'])
            child = base.get_row('Main', child_link['row_id'])
            add_children(child)
server_url = context.server_url
api_token = context.api_token
base = Base(api_token, server_url)
base.auth()

json_data = base.query('select * from Main')
for data in json_data :
    family=[]
    add_children(data)
    base.update_link(
        link_id='5lSC',
        table_name='Main',
        other_table_name='Main',
        row_id=data['_id'],
        other_rows_ids=family
    )

Hope this helps…

Bests,
Benjamin

[Francois]

Thanks a lot Benjamin.
I will look at your workaround but recomputing the values in the python script may be simpler !
Bests,
Francois