Help populating table with applescript

You can use Applescript but you need not. You can use the built-in formulas of Numbers.

Here is one way without Applescript:

The table on the left (titled "Data") is your original data.

The table on the right is the second table you posted with two extra columns (B and C, which you may hide later).

The first row of both tables is a header row.

For the table on the right ("Summary")

column A contains counting values as show.

B2=IFERROR(OFFSET(Data::$A$2, A2−1, 0), "")

this is shorthand for select cell B2 then type (or copy and paste from here) the formula:

=IFERROR(OFFSET(Data::$A$2, A2−1, 0), "")

C2=IFERROR(VLOOKUP(B2, Data::A:C, 3, 0), "")

D2=SUM($C$2:C2)

E2=IF(D2≤24050, C2, "")

select B2 thru E2, copy

select columns B thru E by clicking column B, then hold the shift key and click column E

unselect the header row for these columns by holding the command key and click cells B1, then C1, D1, then E1

paste

The final piece of information is to sort the Data table ascending by the code in the "Value" column (column B). If you need to preserve the entry order add a new column on the very left with a counting value that you can use to resort the table with.

For this kind of problem AppleScript is harder to set up than Wayne's approach but thereafter is easy to run (and, though verbose, for some is perhaps a little easier to understand though that clearly depends on what you are used to). So which is "better" depends on how much you need to repeat this. You can do something like this:

To populate the Results table I first set up an empty table with the Header row and one body row, ran the script below, clicked once on cell A2 and pasted the results the script placed on the clipboard. Numbers is smart enough to expand the table as much as needed.

SG

The script (copy and paste into AppleScript Editor) is as follows. Change sheet name and table name as needed.

property theValues : {"3a", "4", "4a", "5", "6", "7", "10", "10a", "12", "13", "18"}

property maxAmount : 24050

tell application "Numbers" to tell the front document to tell sheet "Sheet 1" to tell table "Table 1"

set {pasteStr, cumulativeAmount} to {"", 0}

repeat with valueCounter from 1 to count of theValues--cycle through 3a, 4, 4a, etc

set valueBeingTallied to (itemvalueCounter of theValues)

repeat with rowCounter from 2 to count of its rows

tell rowrowCounter

set nameInRow to value of its first cell

set valueInRow to my stripDecimal(value of its second cell)

set amountInRow to value of its third cell

if valueInRow is equal to valueBeingTallied then

set cumulativeAmount to cumulativeAmount + amountInRow

if cumulativeAmount ≤ maxAmount then

set pasteStr to pasteStr & ¬

valueInRow & tab & nameInRow & tab & amountInRow & return

else

exit repeat --stop because maximum exceeded

end if

end if

end tell

end repeat --row

end repeat --value

end tell

set the clipboard topasteStr

display notification "Click once in a cell and paste." with title "Numbers"

pasteStr

--handler needed because Numbers adds .0 to anything it thinks is a number

to stripDecimal(val)

set val to val as string

set {oTID, AppleScript'stext item delimiters} to {AppleScript'stext item delimiters, "."}

set s to item 1 of val's text items

set AppleScript'stext item delimiters to oTID

return s

end stripDecimal

consiglieri_swe wrote:

... lets assume for this example that the availabe sum is 36500)

This would mean that the summary table would only list, sorted by legal priority, debts up to and including "Hank" in my example table but not Sue-Ellen or John.

Not sure I follow. Why would Sue-Ellen, with priority 5 not be listed yet Hank, with priority of only 18 , would be listed? Based on your description, if the available sum is 36500, wouldn't the results be as follows?

Is there is another constraint not yet made explicit (creditors of the same priority receiving equal or pro-rata amounts, perhaps)?

SG

consiglieri_swe wrote:

i.e Goofy only gets part of his debt.

The partial payment complicates the logic a little. Below is a script that can handle that. The usage is the same. Paste it into AppleScript Editor. Run. Paste results into A2 of the Results table and the table expands automatically to the number of rows needed. The Sum formula is in a Footer Row.

The revised script, which now reads the available amount from a cell in a table (easily changed to another table as needed), is below.

SG

--accounts for partial payment of "last" creditor

--reads available amount from a cell in a table

property thePriorities : {"3a", "4", "4a", "5", "6", "7", "10", "10a", "12", "13", "18"}

tell application "Numbers" to tell the front document to tell sheet "Sheet 1"

tell table "Maximum"

set maxAmount to the value of cell "A2"

end tell

tell table "Table 1"

set {pasteStr, stillAvailable, allDone} to {"", maxAmount, false}

repeat with i from 1 to count of thePriorities --cycle through 3a, 4, 4a, etc

set priorityBeingTallied to (itemi of thePriorities)

repeat with j from 2 to count of its rows

tell row j

--get the values in a row

set thisName to value of its first cell

set thisPriority to my stripDecimal(value of its second cell)

set thisAmount to value of its third cell

if thisPriority is equal to priorityBeingTallied then --if it's in this priority

if stillAvailable > 0 then --include the row unless no more money

set thisAllocation to my lesserOf(thisAmount, stillAvailable)

set pasteStr to pasteStr & thisPriority & tab & thisName & tab ¬

& thisAllocation & return

set stillAvailable to stillAvailable - thisAllocation

end if

end if

end tell

end repeat --rows

end repeat --priorities

end tell --table

end tell --app

set the clipboard topasteStr

display notification "Click once in a cell and paste." with title "Numbers"

pasteStr

--handler needed because when asked for a cell value Numbers 3 adds .0

to stripDecimal(val)

set val to val as string

set {oTID, AppleScript'stext item delimiters} to {AppleScript'stext item delimiters, "."}

set s to item 1 of val's text items

set AppleScript'stext item delimiters to oTID

return s

end stripDecimal

to lesserOf(x, y) --used to calculate partial payment to "last" creditor

if x < y then return x

return y

end lesserOf

--end of script

Glad it helped. Thanks for the green tick. Here is another approach if you find that in the end maintaining an AppleScript makes you a little nervous. AppleScript is more convenient than formulas for repetitive tasks such as extracting a list of distinct values from a list of names (say Steve or George appeared in the list many different times and you wanted list each only once). But here, while the script does a good job, it seems you can also do the job with two very simple formulas.

Make a copy of your original table, and sort it by priority and name so you have it looking something like this:

The two shaded rows at the top are defined as Header Rows and the Bottom Row as a Footer Row.

Then add two more columns so the table looks like this:

The formula in D2 can refers to another table where you have your "available" total.

The formula in D3, copied down, is =MAX(D2−C2,0)

The formula in E3, copied down, =MIN(C3,D3)

Then for presentation, you can hide row 2 (and column D if you want) and filter out the rows with zeros:

SG