I have an invoicing/lineitems/inventory database. I want to track sales of new inventory, as well as sales of slightly damaged inventory of the same items.

My thought is to have 2 fields in the line items table - QtySoldNew and QtySoldScuff. I want these fields to be mutually exclusive, that is, only one can have a quantity, not both. It seems like I need to have a validation by calculation that involves isEmpty or not isEmplty, but I am not sure what the calc would be.

The idea is to enter the item #, then enter either a quantity under new or scuff, depending on which inventory it would come out of. Then I could set up 2 inventory calc fields in the inventory table to track both new and scuff levels for each record.

Any suggestions would be great.

Thanks,

KC

I'd suggest instead that you use a source field in line items, so that inventory or scuff (scuff? .. never heard that expression) would be used. But do you have an alternate inventory table or how are you handling inventory records anyway?

An auto enter in your field definition would work. Uncheck the box to allow it to always replace.

Those calcs would be:

QNew = Case(not isempty(QScuff); ""; QNew)

QScuff = Case(not isempty(QNew); ""; QScuff)

I planned to use 2 fields in each line item record (one for new and one for scuff) and 2 fields in each product record (new inventory total and scuff inventory total). The inventory levels will calculate from several tables, including sales, returns, consignments, etc.

Do you mean that each line item would have a category field, containing either the text "New" or "Scuff"? I thought about that, but am not sure how to calculate new and scuff inventory in my inventory table. Any thoughts on that would be great.

(The products are books, "scuff" means the book is scuffed or damaged.)

Thank you for your suggestion. I tried these calculations and tested them. I can enter an amount into QNew, then enter an amount into QScuff. The amount in QNew disappears.

However, if I go in the other direction, enter an amount into QScuff first, then an amount in QNew, the amount in QNew still disappears. (I would think this would make the amount in QScuff disappear.)

Any ideas?

If you really want TWO entry fields, you will find a nice tutorial on the required technique here.

I think it would be easier for the user to enter the quantity in ONE field, and check a box if it's scuff. Then you can have two calc fields 'behind the scenes' to figure it out.

I just ran those auto enter calcs again using both the Case and If functions and it seemed to work fine. I don't know why they don't work for you. But if you want to pursue the suggestion of having one entry field for Quantity and one entry field for Type:

You still have your QScuff and QNew fields but the user never interacts with them directly. Instead, they interact with Quantity and Type. Quantity is a number field, Type a text field with a radio button based on a value list of New and Scuff. QScuff = If(Type = "Scuff"; Quantity; "") and Qnew = If(Type ; "New"; Quantity; ""). Then sum QScuff and QNew as needed.

Thank you so much, everyone!! That is so simple - choose New or Scuff, then separate them out with a calc. Sometimes the solution is so obvious, it is easy to miss. Thank you also, comment, for your link to the tutorial. It may be something I can use in the future. Thank you all again!