So I need a little bit of hand holding for a little bit. I am familiar with database theory and I am trying to create a database for a rental company with FM7. My current problem is how to create an invoice where I can add line items from a generic inventory table I've created based on common rentals and common formatting proceedures we've been typing in by hand.
I realize that the Item:Invoice is a m:m relationship and it probably needs a join table, but I am unfamiliar with how to create this join table in FM and how I would display the records in each other's table. I also need the information in the Invoice Item list to be directly editable since a lot of our invoices need tweaking to tailor specific needs to customers. For example, in my inventory list, a drum kit for rental will read specified drum sizes as X" instead of 12" for later editing once it is already in the invoice, so it cannot change the formatting in the inventory, just the invoice.
I am wondering if there is a way to do this with a join table and portals? Or maybe a script? I'm open to any ideas that will allow editing in the invoice fields that will not affect the inventory records as well.