Hi All,
I’ve found several examples that are close to what I am trying to do, but I can’t get any of them to work. Essentially, I have a table of projects with different types of billing options and a table of invoices. What I am trying to do is to have a button in the projects row that creates a number of invoices in the invoice table based on the type of billing. For example, if the project is billed in thirds, the button would create 3 new rows in the invoice table with the amounts. If billed in half, then the button would create 2 new rows. In both cases, the new invoice amounts need to be pulled from the columns in the projects table for that row.
The current doc dosen’t work because I think the conditionals aren’t right. If you remove those, then the button works but it places the name and amounts in both the name and amount columns of the invoices table rather than the name in the name column and amounts in the amount column.
Finally, in the case of a retainer type, the button should create however many rows are specified in the RetNo column.
Here is a link to the doc: https://coda.io/d/_daIDJTjCgkv/Multi-Line-Add_su9wQeCB
Any thoughts?
Thanks! -b
1 Like
Hello,
What would be the amount you would need in the Amount Column of the invoice table if the project is RetNo? Just the In1?
But also, I don’t think I’d set it up like this, I think I would just make a relation between the two and have a button that adds a new Invoice and asks for what amount you’d need. The way you have it now isn’t very scalable.
Hi Sam,
Thanks for your help.
Yes, the amount would be Inv 1 for the retainer times RetNo for the number of rows.
The reason it is set up like this so so when the project the invoices are created as a reminder to send them when the time is right and to track upcoming billings vs current vs past due. This is example is part of a much larger doc, but I like to work out things first on their own before implementing. I hope that makes sense.
-b
Then I’d make two buttons on the projects table. Use the formula’s below. Disable the first if RetNo is not blank and disable the second one if the RetNo is blank.
I’m rereading it now and don’t think I fully understand how you wanted the RetNo to work. In my example, for Project 5 with RetNo of 5, it will create 5 rows on the Invoice table, each with name Project 5 and Amount for Inv1. Is that what you need?
No, you got it right. I went ahead and wrapped the entire thing in an If statement so it can be done with one button.
If(thisRow.Type=Retainer,
ForEach(Sequence(1, ToNumber(thisRow.RetNo)),
AddRow(Invoices,
Invoices.Name,thisRow.Name,
Invoices.Amount,thisRow.Inv1))
,
List(thisRow.Inv1,thisRow.Inv2,thisRow.Inv3).Filter(CurrentValue.IsNotBlank()).ForEach(
AddRow(Invoices,
Invoices.Name,thisRow.Name,
Invoices.Amount,CurrentValue
)
)
)
The only issue now is how to disable the button after the invoices are created.
Final update:
I added a new column called InvDone as a checkbox, wrapped the entire block in a RunAction function which checks the box and creates the new rows in the other table .
RunActions(
If(thisRow.Type=Retainer,
ForEach(Sequence(1, ToNumber(thisRow.RetNo)),
AddRow(Invoices,
Invoices.Name,thisRow.Name,
Invoices.Amount,thisRow.Inv1))
,
List(thisRow.Inv1,thisRow.Inv2,thisRow.Inv3).Filter(CurrentValue.IsNotBlank()).ForEach(
AddRow(Invoices,
Invoices.Name,thisRow.Name,
Invoices.Amount,CurrentValue
)
)
),
ModifyRows(thisRow, Amounts.InvDone, "true")
)
https://coda.io/d/_daIDJTjCgkv/Multi-Line-Add_su9wQeCB
Yeah, I personally like to break things out into different buttons to show visually what is happening when I’m sharing the doc with coworkers, then I’d make a third button to press both of the make invoice buttons so I can get the results in a results column. But your set up is great too.
Hope it was helpful, happy to answer any more questions.
Hi Samuel,
Actually, I do have another question. I need to pass additional columns in the row to the invoices table, but when I try to add them to the row, the resulting table ends up with more rows and the same data in both columns. How can I pass additional content from each row?
It depends how you have your button set up. In this case, you should just be able to reference the values you want using thisrow.[column name]. I added a date column to your project table, then modified the button to look like this:
I pressed the button for row 2, and got these two rows on the invoices table.
What did your button look like that was giving you the extra rows?
I did basically, what you did, but I can only get it to filling in the Phase field with one of the phases. How it needs to work is just like each new row is pulling a different amount based on the column, it should also pull the phase for each Inv1Phase, Inv2Phase, etc. Look at it now. I also tried to add Inv1Phase, Inv2Phase, etc. to the list, but then it creates additional row.
This is part of the reason I would recommend going about this differently and having not having so many columns in the project table.
In your formula, you have
But the CurrentValue refers to the current value in the list of invoices. So what you would need to do is write it like this.
But you can see how this would get complicated because you’d need to write a switchif() to see which Phase you need to be using.
I just don’t understand if a list is made the Inv amounts, why can’t the phases be pulled from the list like the amounts? I get the logic of how this works, but not how the list is parsed.
The other way I could do it is to concatenate the two values and then split them in invoice table. Not ideal, but that would work.
Also curious how you would set this up too!
Thanks for your help.
I’m not sure if you’re able to see the changes I made, but I added a a button to the Companies table that allows you to add a new invoice:
I would also restructure the tables to be Company, Project, Invoices. Then your relations would be like this:
Then that button to create an invoice would be put on the projects table instead of the company table.
I think I accidentally overwrote your work. Your set up is how this is set up. The button is in the Projects table (it just wasn’t named that). Look at how it is now, it works, but not it isn’t taking into consideration the blank values so it’s always making 4 new rows.
Coda sees white space as content in a column. Your formula adds a space:
I added .substituteall() to the list filter and removed all white space.
Got it. Thanks again for your help!
1 Like
