I’m trying to achieve something close to what’s suggested in many topics on this forum but never really answered in a way that suits my need. : populating a table with data from another one containing templates, both tables sharing the same structure. It’s like a full row copy, pasted in a new row of another table.
Let’s be more specific :
I have two tables :
a huge one with a row per batch produced
A table with the same structure, containing template recipes
Nowadays i select one row from the recipe table, copy it, paste it in a new row in the production table, adjust a few things by hand (change the batch date, the batch number…) and i’m good. That’s the behaviour i’d like to automate by for example having action buttons on the recipes page “new recipe1 batch”, “new recipe2 batch”, etc.
I would create a helper table where you specify which template you want to copy into your huge table (a dropdown reference from your template table) and the few things that you adjust by hand. The Action button would read those “variables” and use LookupOne or LookupRecords to retrieve your template recipe and add to your huge table. The helper table should also include the button and actions columns that the Action button requires. The actions column is where all of the code will go to do the heavy lifting for inserting new rows of data into your huge table.
I would use the LookupOne function to find the row id in your template table that matches the template you select in the dropdown reference of your helper table. That will get the entire matching row into a Python list, which you can then use dot notation to reference each column in your template table.
You’ll then start building out the data to populate in your huge table. In the example the line act = ["AddRecord", "Schedule", None, {"Account": Loans.lookupOne(Account=acc).id,... is how you define what actually gets populated in your huge table. The part inside the curly braces is JSON key pairs specifying the column name and corresponding data to populate in that column.
If you’re looking to populate multiple rows at one time, you would use a Python loop, with each loop being a new row in your huge table. Depending on how you determine how many rows you want to add, you could have another column (numRows) in your helper table that you specify how many rows you want to add. In the case of just specifying a number of rows, you could then use a for or while loop like:
i = 1
while i < $numRows
act = ["AddRecord", ...
i += 1
There’s a lot more to finishing the code examples I provided, but that should get you headed in the right direction.
Thanks for all your insights and knowledgeable tips, however action buttons don’t seem to work no matter how hard i try to convince them… I’ll continue testing.
What version of Grist are you using? I’m assuming the Grist hosted version…?
What is or is not happening when you try implementing the action button? If you’d be willing to share your project, I’d take a look at what you have and be able to provide better advice. I wouldn’t write all your code for you, but it would make it easier to guide you in the right direction.
Or at least provide some code snippets, here, for what you have in the button and actions columns.
One thing I didn’t discuss was adding the actual custom widget. It needs to be on the same page as the (helper) table that contains your button and actions column. I say that, but, (i think) the action button widget just needs to have the source data table set to your helper table, and doesn’t necessarily have to be on the same page, although it seems more intuitive to have them on the same page. Then, in the widget settings, you need to ensure the access level is set to “Full document access” and in the Action dropdown, that you’ve selected the “button” column from your helper table.
Well i must have had an error in my code, i remade a very basic version of it all and now it works !
Now for the fun part : making it work the intended way. Indeed a helper table sounds smart, i would add an initilization formula to make it go back to a blank state after each action button click so it cannot add a ton of line if one spams it… I’ll log some of my progress here and rest addured that i’ll be back for more questions
My goal is to avoid anything “pretty advanced formula magic” as i will not be the only one using and ultimately maintaining the document. So the simpler the better.
I have crafted a template selector that pulls data from a template table, copies it where i cant it to go and resets to null after the action button is pressed (so you cannot spam the button), and i think it is pretty elegant codewise too !
When i’ll be a bit more ready i’ll share a version of my work.
