I'm trying to determine how to create an invoice from the information on our orders table but the two options I've thought of both have their issues.
1. Option 1: Create a second form for my orders table with only the specific information needed to create an invoice plus additional fields for exact charges (versus estimates which are found on the orders table.) When I created this second form and added the fields I needed, I was unable to "edit" the form (i.e. fill in the new information) when I clicked "edit." The page refreshed back to the main orders form instead of the invoicing form I was on. Is there a way to fix this?
2. Option 2: I created a new table for invoicing and a table relationship to the orders table referencing only the fields needed on the invoice. I then attempted to make a Report Link field to show the products from that related order (the orders table has a relationship with the products table; many products to one order) but after creating the field relationship (matching the order # from the orders table to the order # on the product table) the embedded report still showed up blank. ("No products found.") Am I linking this field incorrectly?
Our products are purchased from an outside manufacturer and brought into our inventory. On the orders table we "add products" and within the product table we "add an inventory adjustment" to show the actual quantity and weight purchased into inventory. Once we sell these products to a customer we "add an inventory adjustment" from the orders table to select the item, quantity, and weight we want to sell from our inventory. The purpose of the invoice form or table would be to access the products related to that order and enter the inventory adjustment as either a purchase or a sale.
I am also open to an Option # 3 if you have an alternate suggestion. I tend to overthink this and make it way more complicated than it needs to be. Thank you.