It can be done, but as far as I know, you have to write a script that checks for whether the added line item is a "package" or "kit" and if so, adds the addtional items to your line items portal. You'd then have to design your layout to make sure this script is performed each time a line item is added to your invoice--script triggers are one way to do this.
I've been pondering this for a while now and I just can't seem to wrap my brain around it. In theory, I can think of how this would be done but I would need to use an array, which Filemaker just doesn't do. The issue is, I want a use to be able to create a package. If I created the packages, I would be able to script something that would insert each record, but I cannot think of a dynamic way of doing it.
However, I have brainstormed an idea of somehow creating a set of line items for the packages. (ie. create a set of line items that include each product in a package) and then be able to somehow add those items into a new record. Is there a way to merge or copy records like this?
I know what I am thinking, but I am not always able to express it. I appreciate the help, and would be glad to try and clarify more if needed.
Usually, an Invoicing system consists of three tables Invoices, LineItems, Products.
A portal to LineItems is usually placed on an Invoices layout to record the sale of each type of item purchased. Item names, pricing etc is usually looked up from the Products table as an item number is entered/selected in this portal.
For kits you add another field to Products that's empty unless it is a package (also called a "kit") item. Then you define an extra table that you use to list all your kit items.
Your Packages Table would just have three fields: Qty, PackageID and ItemID. (both PackageID and ItemID are ID numbers from the ItemID in Products)
In your Products table, you'd have a specific record for each package with the components listed in the Packages Table.
Example, Item 2345 is called Picnic for two Kit.
In packages, you'd record:
Qty PackageID ItemID
2 2345 8921 (8921 in Products is the number for a dinner plate)
1 2345 8922 ( ID for a bottle of wine)
2 2345 8923 (ID for a wine goblet)
When your script identifies a newly added item as a Package item, it uses a find or Go To Related Records to pull up the set of itemIDs for that package and loops through them using Set Field and/or Set variable/set field to copy the ID numbers and quantities into new line item records on your invoice.
this is exactly what i am looking for, although i think i need a bit more instruction on how to achieve this.
i have the 'invoice-<line item>-product relationship' happening.
from what i have read in this thread, i need to make a 'package' item in the product table and add an empty field that only gets populated for packages only.
next make a package table with the three fields.
my question is how does the relationship of this new table work? is it related somehow or is it devoid of relationship and just used to reference package items?
i need to work this one out as this article is exactly what i am looking for. thanks!
This is exactly what I need. I am trying to get this script right it damaged my database the way i wrote it. what am i missing?
I also want to make sure i have my relationship right
Threads this old (3 months or more) no longer appear in recent items so they are easy to miss. You can always open a new thread and include a link to an older one if appropriate so that your question is easier to see by other interested parties.
No script should ever damage your file. More likely, your file was already damaged and the script encountered that damage in a way that made the previously hidden damage apparent to you by locking up or generating a message that the file is damaged.
In this case, you have an infinite loop, as the Exit loop step checks to see if $INVID is empty, but no step inside the loop modifies this field so the loop either never executes or executes infinitely.
I'd think your script should work like this:
#Script starts from layout with portal to line items and focus is in a row of the line items portal
If [not isEmpty ( Inventory;:Package ) ]
Set Variable[$EventID ; value: LineItems::EventID]
Set Variable [$PackageQty ; value: LIneItems::Quantity]
Go To Related Record [Show only related records; From table: Inventory Package; Using layout: "Inventory Package" (Inventory Package)]
Set Variable [$InvID ; value: Inventory Package::INV ID]
Set Variable [$Qty ; Value: Inventory Package::Qty]
Go To layout [LineItems]
Set Field [LineItems::EventID ; $EventID]
Set Field [LineItems::Inventory ID ; $InvID]
Set Field [LineItems::Quantity ; $Qty * $PackageQty ]
Go To layout [Inventory Package]
Go To Record/request/page [Next ; exit after last]
Go To layout [original layout]