is an empty field different than value of 0?

[Archit3kt]

I have a database serving as a requisition form for kitting parts for manufacturing--there are 10 text fields to specify items (nuts, bolts, washers), and 10 fields for the quantities of those items. Part of a script determines the item with the lowest quantity, returns the name of the item, and then subtracts that quantity from all the other items. The user will not always use all ten fields, and I want to tell the script to skip fields that are empty, but it needs to treat fields with a value of "0" differently, and include them in the rest of the calculations. Will that work? the pertinent steps will look something like this:

if ( ::part1 ≠ "")

set field ( ::part1 ; Value:$qty1 - $minqty )

end if

if ( ::part1 = 0 )

some step to change to the next lot code

end if

so, if the field ::part1 is empty/unused, I want it to skip both calculations.

[comment]

-there are 10 text fields to specify items (nuts, bolts, washers), and 10 fields for the quantities of those items.

This is not a good method overall. You should use records in a related "line items" table instead of multiple alike fields. I am afraid I didn't get the rest of your question.

[Vaughan]

if ( ::part1 ≠ "")

Do NOT use ="" or ≠"" to determine whether a field is empty or not. Use the IsEmpty() function.

[Archit3kt]

sweet, I'll use IsEmpty()...

I agree with you, comment, that related records in a line items table is much cleaner. However, I'm not sure how to handle this aspect: one "part" (a record in the related line items table), say a nut or a bolt, needs to be related to many records in the first table, because that first table is a "build order form" that gets printed out every day, and is a running history of using various combinations of these same parts. If we change one of the parts, say, we decide to make the bolt out of a different metal alloy, and change the record in the "line items table", that record change will be reflected in all the previous "forms" (records) that referenced that record, effectively erasing history of the previous bolt that was used.

....and we frequently change parts and suppliers, because we are a rapidly growing business, experiencing major growing pains...

i mostly wanted this form to be in FMP and not just a template in Excel because I want it to automatically export and save records in pdf, to take administrative burden off our workers, and to automatically concatenate the Lot Codes we use to trace our parts through manufacturing.

[comment]

I am afraid I am still not following you. It seems that a "build order form" is something like an invoice, and a part is something like a product, and you should use a "line-items" table to facilitate a many-to-many relationship between the two - just like a standard invoicing solution does.

[Vaughan]

If we change one of the parts, say, we decide to make the bolt out of a different metal alloy, and change the record in the "line items table", that record change will be reflected in all the previous "forms" (records) that referenced that record, effectively erasing history of the previous bolt that was used.

If a bolt is changed, then a new "product" record should be made for it.