Fireworks companies that keep inventory records in an external inventory management system or in Excel can import their inventory records into Finale 3D as described here but they then face a puzzle when their inventory records are revised in their inventory management system: how can they update the inventory stored Finale 3D with up-to-date information from their inventory system without overwriting any fine tuning to simulations they may have made directly to the records stored in Finale 3D.
The most common fields to update regularly are “Price,” “Description,” and “Available”. The Available field is the item quantity available for scripting, which companies usually calculate as the Quantity On Hand minus Quantity Reserved plus (maybe) Quantity On Order. You can use any calculation you want. From the perspective of Finale 3D, if you are using an external inventory management system the Available field is just a number. You may notice Finale 3D also has a “Quantity On Hand” column, but that column is reserved for the Finale Inventory integration available from our sister company, finaleinventory.com.
Figure 1 – Importing effects into an effects database will overwrite any matching effects entirely, including any adjustments you may have made to their simulations
One other quantity column that is available for you to use is the “Quota” column. Like the Available column, the Quota can be used to filter the effects lists to items that have Quota > 0, as well as other filtering criteria. Also like the Available column, you can import any quantities you want into the Quota column, by whatever calculation you want, including for example, if you wanted to use the Quota column to hold the Quantity On Hand or Available or Reserved or other item quantity calculation from your external inventory system. There is one significant difference, though, between the Quota column and the Available column: the Quota column is saved as part of the show file, whereas the Available column is saved as part of the effect database. The intended meaning of Quota is “the item quantity target for a particular show,” which explains why the Quota is saved in the show file, not in the effects database. You can import the Quota from the menu item, “File > Import > Import quotas…” The import operation will have no effect on the effect database or on any other fields. It therefore will not affect any simulations you may have modified in your effect database.
Figure 2 – Imported quotas are saved with the show; importing does not affect the effects database.
So, if the only field you need to update is a quantity field, and if you don’t mind importing it as part of every new show, then the Quota column may be an easy solution for you. If you need to update prices or any other fields, then we still need to solve the puzzle of importing the revisions without blowing away any updates to simulations you may have done in Finale 3D.
The “File > Import > Import effects file…” is an all-or-nothing proposition for each row. If the row has part number that matches a part number in the effects database you are importing into, the row will overwrite all fields in the effect database, including the VDL fields or any other fields that you may have customized in Finale 3D to fine tune the simulation. Consequently, re-importing your external inventory into an existing effects database in Finale 3D is not a great option if your effects database in Finale 3D has any information in it that you don’t want to overwrite.
To update a specific field without overwriting other fields, the best option is to lean on the copy/paste functionality in Finale 3D, and its compatibility with Excel. The instructions are,
- Open your external inventory records file in Excel.
- Sort by part number.
- Select the column of data cells in Excel that you want to copy into Finale 3D, e.g., Price, Description, Available, etc., by clicking on the top cell and dragging down. Do not include the column header cell in the selection. Press control-C to copy.
- Turn your attention to the effects window in Finale 3D, and select your effects database from the blue selector in the upper right.
- Sort your effects database by part number in Finale 3D by right clicking the part number column and selecting “Sort by this column.”
- Select the column in Finale 3D that you want to replace by right clicking on the column header and doing “Select this column.”
- Press control-V to paste. As a result, all the rows will turn fully yellow to indicate they were modified, and the pasted values should appear in the column that had been selected.
- Confirm that you have the same number of rows in Excel and in your effects database in Finale 3D, and confirm the sorts are the same. Note that Finale 3D sorting is case-sensitive. Excel‘s sorting is generally not case-sensitive, so if you have upper and lower case letters in your part numbers, you may need to sort by another column to ensure consistency. You could use the “Custom Part Field”, for example.