Client: 213W Wine Imports

Project: Custom FileMaker Pro Database System

Subject: Build x07

Date: 7/28/2011

There are 11 agenda items addressed in this Build x07 . . .

1. ORDERS LINE ITEMS FIELDS NOT POPULATING WITH AUTO-ENTER DATA

1.1 Issue

alcohol content and price don't come through on look-up alcohol does not come in consistently, if i add a 23 to the isoocles and ripple 4...for example...all comes in as 23  sometimes nothing, alcohol and price don't come through on look-up, alcohol does not come in consistently, all comes in as 23 (ripple has 4 and comes in as 23 (Justin), sometimes nothing.

1.2 Correction

The "Alcohol" and "Price" fields in the Orders Line Items Table (OrdersLN) fields are number fields formatted with an auto-enter calculation formula that draws pre-existing values from the Inventory Table. The calculation formula referred to an incorrect table occurrence. It has been revised with the correct calculation formula and both fields now work properly.

1.3 Screenshots

The following screenshot display the steps taken to correct the calculation formula...

2. ORDERS | TOTAL SALES TAX CALCULATION DOES NOT REFRESH

2.1 Issue

tax calculation does not function is not correct and does not update

2.2 Correction.

The calculation for sales tax of an order is based on the combination of the sales tax rate entered for the order AND if the "Taxable" checkbox in a line-item of an order has been enabled. The calculation is actually working properly, however the calculation is an non-indexed value so the total sales tax does not refresh normally. To correct the issue, I modified the script trigger formatted on the checkbox field so it "refreshes the window" and now the total sales tax field will update when any line-item taxable checkbox is enabled or disabled.. I also modified the taxable field in the Inventory table so the checkbox is normally enabled.

2.3 Screenshots.

The following screenshots display the steps taken to correct the issue.

3. SALES ORDER & INVOICE FORM TOTALS NOT IN-SYNC

3.1 Issue.

pdf /sales invoice function does not give proper invoice

3.2 Corrections.

The fields for finance, discount, and sales tax in the Orders table and the Invoice table are percent number fields, however the decimal point was different between the Sales Order table and the Invoice tables. I reformatted all the percent number fields to match the same decimal position and to display the percent symbol in the field in both tables. I also redesigned all applicable layouts (ie ledgers, journals, forms) to display these percent number fields with the percent symbol. I also revised the print layouts for all forms to display these fields with the revised decimal position. I also revised all the calculation fields that reference these percent number fields to calculate based on the revised decimal position. Also, since we are only using one sales tax value, the previous references to a tax 2 and a tax 3 on all these layouts has been removed. Also, the calculation formula for the total of a Sales Order form and an Invoice form was revised so any tax is charged against the subtotal AFTER any discount amount is taken.

3.3 Screenshots.

The following screenshot display the steps taken for the above...

4. EMAIL CAMPAIGN TEMPLATE SELECTION

4.1 Issue.

The Email Campaign Template Selection is not working

4.2 Corrections.

When selecting an email template from the portal list, a field is set that triggers an auto-enter calculation to several fields. When the separate "email campaign business template" file was consolidated into the main database file, the calculation formulas for these fields were set as comments verses calculation formulas. I corrected all the calculation formuals so now the routine for selecting email templates works correctly

4.3 Screenshots.

The following screenshot display the steps taken for the above...

Layout

Fields (with Auto-Enter Calculations)

Calculation Formula (broken)

Calculation Formula (fixed)

5. EMAIL CAMPAIGN SEND CAMPAIGN (to GROUP RECIPIENTS)

5.1 Issue.

(Frank) "…email not functioning (?) all mail goes to dave haney"

5.2 Corrections.

The script to "Send Campaign" attached to an Email Icon on the Layouts, had a broken script step that needed to be fixed.

5.3 Screenshots.

The following screenshot display the steps taken for the above...

Layout

Script (broken)

Script (fixed)

6. INVOICE DATE and DUE DATE (Date Format)

6.1 Issue.

(Frank) "invoice date to due date different format"

6.2 Corrections.

The forms in the accounting template are set to a short date format, while the Sales Order form is set to the longer date format. When you run the sync routine between the Sales Order template file and the Accounting template, the due date comes over with the long date format. I changed the format for all dates in all of the forms in the accounting module to use the longer date format.

6.3 Screenshots.

The following screenshot display the steps taken for the above...

Layout (incorrect mismatch date format)

Layout (correct matching date format)

7. PROCESSING A SALES ORDER (Checking Inventory Stock)

7.1 Issue.

(Frank) ".... sales number and bottle count function script does not work .... check stock does not work in that there is no update on the inventory (does not subtract) .... color code on check stock is not functioning (when the stock is ample, the color is green, when out of stock red) .... check stock does not work in that there is no update on the inventor"

7.2 Summary.

Most of the routines for processing a Sales Order are not completed or broken. For example, here's a broken script that needed to be fixed ....

Script to check stock (broken script steps)

Script to check stock (corrected)

8. PROCESSING A SALES ORDER (Layout Design)

8.1 Layout Modification.

I re-organized several objects on the Sales Order Layout that were previously scatter around the bottom of the screen. They have been moved into a section called "Processing" ...

Layout (redesigned with "Processing" section)

8.2 Change from Radio Buttons to Checkbox for Invoice Status.

There were Yes / No radio buttons for the status of Invoice Paid and Invoice Sent. I changed these to Checkboxs and attached scripts as Script Triggers to run an existing routine that simply post amount due on the sales order record. It would be better to track anounts due in the accounts receivable ledger account record in the accounting module verses on a Sales Order, but I left the existing routine to post an amount due based on enabling or disabling a checkbox.

Scripts (old Radio Button scripts and fields)

Scripts (replaced with Script Trigger routine)

9. CHECK STOCK (Resets Number in Inventory Record)

9.1 Issue.

(Frank) "sales number and bottle count function script does not work . . . check stock does not work in that there is no update on the inventory (does not subtract) . . . sales number and bottle count function script does not work . . ."

9.2 Summary & Correction.

The routine has buttons on the Sales Order (Form View) Layout for "Check Stock in Bergen" and "Check Stock in Oslo" which runs a script to (1) reset the number of bottles in a field for the respective location in the Inventory record for the Product Item; and (2) resets the font color in the Sales Order "Line Item" for the Product Item. The scripts rely on finding records based on relationships, and calculations based on relationships that were broken. The scripts and calculation formulas are now fixed.

9.3 Screenshots.

The following screenshot help define this routine, and the steps taken to correct the scripts & calcs ...

Sales Order (Form View) Layout (Check Stock Routine)

Script (Check Stock in "Bergen")

This script (1) reset the number of bottles in a field for the "Bergen" location in the Inventory record for the the Product Item in the Sales Order Line Item record; and (2) resets the font color in same Sales Order "Line Item" based on the number of bottles in the location. If the number of bottles is less than zero, the number is reformatted in "red".

10. INVENTORY (Bottle Numbers per Location) & (Record Navigation)

10.1 Form View and List View Layouts.

Below are screenshots of the revised List View and Form View layouts for Inventory records, showing the number of bottles in each location, and the total; and new Navigation & Command buttons that are also on the Contacts and Sales Order screens.

Inventory (List View)

Inventory (Form View) Layout

11. CHECK STOCK (Resets Number in Inventory Record)

11.1 Issue.

(Frank) ". . . packing slip info does not correlate with invoice . . ."

11.2 Summary & Correction.

The layouts to print a sales order form and a packing slip form had missing fields and/or borken relations so data did not appear or appeared incorrectly. Also, the routine to process forms from the Sales Order screen was a poor design and incomplete.

I fixed all broken relationship, added missing fields, and redesigned the layouts for printing out both the Sales Order form and the Packing Slip. I also revised the routine for processing forms from the Sales Order screen so you first View the respective form in a new popup window, and then from the popup window, you select to either Print to a printer, Save to a PDF, or Email a PDF to the buyer.

Now the look and routines to process both the Sales Order form and the Packing Slip form are exactly the same.

11.3 Screenshots.

The following screenshot help define the revisions to Layouts, Scripts for processing these forms

Sales Order (Form View) Processing Forms

Packing Slip (Old)

Sales Order (Old)

Packing Slip (Revised new popup window)

Sales Order (Revised new popup Window)

Scripts (revised for processing forms from Sales Order Screen)

(END OF AGENDA ITEMS FOR BUILD X07]