ChatGPT for creating Python Code at GRIST? How to prompt it for better results?

[RogerioPenna]

I have been struggling a lot for ChatGPT to create the correct code for the stuff I want Grist to do.

It will usually build Python code expecting libraries to be used, or SQL, etc.

For example…
“in Python, how to get the first record of a table?”

CHATGPT: To get the first record of a table in Python, you can use the get method with no arguments. For example:
my_table = my_base.getTable(‘My Table’)
first_record = my_table.get()

This will return the first record of My Table . If the table is empty, get will return None .

**It’s actually a Trigger formula in a system that works like a database and where I can set the values for the fields in the records. **

But I can also set trigger formulas for the columns in Python.

So this column has a reference to a table called Estagio_Leads. Each time a new value is inserted in the current table, that cell’s trigger formula will trigger and call the first value in the table Estagio_Leads, whose value is “1 - Novo Lead”.

I am not using any PyPI nor other Library.

ChatGPT
To achieve this, you can define a trigger formula for the column that references the Estagio_Leads table. The trigger formula should call the lookupRecords() function to get the first record in the Estagio_Leads table, and then return the id of that record.

Here’s an example trigger formula that you can use:
first_lead_stage = Estagio_Leads.lookupRecords()[0]
return first_lead_stage.id
This code will get the first record in the Estagio_Leads table, and then return its id value. Note that you may need to adjust the table and field names to match your specific setup.

got an error TypeError : ‘RecordSet’ object is not subscriptable

CHATGPT: The error message “TypeError: ‘RecordSet’ object is not subscriptable” usually occurs when you try to access an item in a RecordSet using square brackets, but the RecordSet object doesn’t support that operation. To fix this error, you can try using the get() method of the RecordSet object to retrieve the first record. Here’s an example:
lead_estagio = Estagio_Leads.get()[0]
In this code, the get() method is used to retrieve the first record from the Estagio_Leads table, and then the [0] index is used to extract the first value from that record.

used the code lead_estagio == Estagio_Leads.get()[0], but got a NameError: name ‘lead_estagio’ is not defined

CHATGPT
It seems like you haven’t defined the variable lead_estagio before using it. You can define it before using it in the trigger formula. Here’s an example of how to do it:

define variable lead_estagio and set it to the first record in Estagio_Leads

lead_estagio = Estagio_Leads.get()[0]

define the trigger formula for the column

def trigger_formula(record, column):
# set the value of the column to lead_estagio
column.value = lead_estagio
Make sure to replace column with the actual name of the column in your code.

If I use Estagio_Leads.get()[0] it says usertable has no attribute ‘get’.

what if I try to use the first ID?

…
after some 4 more prompt exchanges I got this

and gave up.

The correct code was MUCH simpler and I got in this forum.

=Estagio_Leads.lookupOne(id=1)

What am I missing on how to ask ChatGPT to give me simple answers that work with GRIST?

I asked it what Prompt should I make for it to give me a simple answer like that one above. This is what it wrote:
“What’s the simplest way to retrieve the first record from a related table called ‘Estagio_Leads’ in a trigger formula in the current table, without using external libraries or complex code?”

However, in a new chat, I used that prompt and the answer was for Excel or GoogleSheets or a relational database in SQL. And when asking
"“What’s the simplest way to retrieve the first record from a related table called ‘Estagio_Leads’ in a trigger formula in the current table, in Python, without using external libraries or complex code?”

The answer was AGAIN quite complicated (and possibly wouldn´t work with GRIST)

import sqlite3

# connect to the database
conn = sqlite3.connect('your_database_name.db')

# create a cursor object
cursor = conn.cursor()

# retrieve the first record from the related table 'Estagio_Leads'
cursor.execute("SELECT * FROM Estagio_Leads LIMIT 1;")
record = cursor.fetchone()

# close the cursor and the database connection
cursor.close()
conn.close()

# print the retrieved record
print(record)

[RogerioPenna]

More specifically. Right now, I have a table called BUDGET, which I will use to create budgets for clients.

In each budget, I select a YEAR PRICES TABLE (one for each year). The prices table is connected to a n-n table where I have the period, the item and the price of the item in that period.

So, I also have the ITENSBUDGET table, which references the BUDGET table.

Thus, I select a budget and include several items. The price of the iteam however must be the one from the period selected in the budget.
Here the budget on the left and on the right the itens list.

Here the prices table. So, in the itens list, I selected concrete code C25. And in the budget the Price Table from March 2023. Therefore, price per unit should be R$11 m³ (fictitious price right now)

What is the formula I should use? I will post here what CHATGPT will tell me.

[RogerioPenna]

@RogerioPenna

I have a table called BUDGET, which I will use to create budgets for clients.

In each budget, I select a YEAR PRICES TABLE (one for each year). The prices table is connected to a n-n table where I have the period, the item and the price of the item in that period.

So, I also have the ITENSBUDGET table, which references the BUDGET table.

Thus, I select a budget and include several items. The price of the iteam however must be the one from the period selected in the budget.

ChatGPT created me a Python function

import sqlite3

def get_price(budget_id, item, period):
    # Connect to the database
    conn = sqlite3.connect('your_database_name.db')

    # Create a cursor object
    cursor = conn.cursor()

    # Execute the SQL query with the parameters
    query = """
        SELECT price
        FROM PRICES
        WHERE year_prices_id = (SELECT year_prices_id FROM BUDGET WHERE id = ?)
        AND item = ?
        AND period = ?
    """
    cursor.execute(query, (budget_id, item, period))

    # Fetch the result of the query
    result = cursor.fetchone()

    # Extract the price from the result (assuming the 'price' column is the first column in the result tuple)
    price = result[0] if result else None

    # Close the cursor and the database connection
    cursor.close()
    conn.close()

    # Return the retrieved price
    return price

which then I must call with these parameters

price = get_price(budget_id=1, item='item1', period='Q1')
if price is not None:
    print(f"The price is: {price}")
else:
    print("No price found.")

so another fail.