Jump to content

Mutually Exclusive Fields


This topic is 5783 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 5783 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.