Tracking purchases

tyler.mitchell

Trying to assist my purchasing department... I've set up a sheet for a department to enter their purchases, but my issue is that our purchasing dept wants to have it itemized. For example, I might have an Amazon order for $1000.00, but that $1000 is comprised of 1 printer that's $500 and a computer that's $500. I'd like to be able to have a place on my form to add individual items that add up to the $1000. It'd be best if it would go below the item line, almost like indented lines saying item 1, item 2, etc. 

 

Not sure if this is even possible, and I'd love to hear from the community on how other facilities have used this tool in this way.

Mike Wilday

What you are wanting isn't possible through a form. A form populates a single row of data.

If you want the itemized items to appear in the same row, as separate purchases this would be possible... You could have subitem1, subitem1 cost, subitem 2, subitem2 cost, subitem 3, subitem3 cost, subitem 4, subitem4 cost. But the end result would be one row, with multiple sub-items. You could have several sub-items... but it wouldn't be indented in the row.

If they want them itemized beneath you could use the form to submit several rows, then manually indent the rows after you have input them individually on the form. You could have an identifier like a checkmark or something to indicate it is an itemized purchase - and then train the purchasing department to indent them together underneath a parent row.

Chris McKay

Hi Tyler,

What Mike said about this not being possible in a web form is correct. However, if you are OK with using a sheet, I would handle it like this:

                                         Cost        Qty      Total

PO # or PO desc        |                 |          |  =SUM(CHILDREN()) / 2

       Item 1 (indented)  |  $500.00  |    2    |  $1,000.00

       Item 2 (indented)  |  $250.00  |    1    |  $250.00

PO Total                      |                 |          |  =$[Total]2

 

The above assumes that Cost, Qty & Total are on line 1. Feel free to change the Total formula to reference another line if you need more information above my Line 1.

We also need to include "/2" in the SUM formula as the $1,000/$250 figures and the =$[Total] figures are children of Line 2 (where the SUM formula resides). If we don't do this, the SUM cell will return a value of $2,500 instead of the expected $1,250.

Once you have created the sheet, simply change the font colour in the cell containing =SUM formula to the background colour (to hide the formula).

I've attached an example of a sheet I created that uses exactly this method of calculating items as a component of a larger figure.

Hope the above makes sense and is of some help to you.