7 Replies Latest reply on May 3, 2014 8:35 AM by ErikWegweiser

# Need help with inventory related fields

Hi

I'm very new at filemaker and have 2 questions

1.- I am making a program for a dresses company, where they produces their

clothes and then they have stores. I need to create the inventory but I

need to know in witch stores they have each product, (a detailed inventory

by store and also a general inventory).

Do I have to create a table of stores and with what fields IDs I have to

related to?

2.-Each sales employee have a commission, based on the amount they sell.

for example.- If she sells under \$1000 she gets the 5%

If she sells between \$1001 and \$2000 she gets the 7%

If she sells over \$ 2001 she gets the 10% commission

My question is how do I can make this calculation for each employee. Do I

first need to add a table of employees?, If yes, do I related them with

invoices?

• ###### 1. Re: Need help with inventory related fields

vcoctel wrote:

1.- I am making a program for a dresses company, where they produces their

clothes and then they have stores. I need to create the inventory but I

need to know in witch stores they have each product, (a detailed inventory

by store and also a general inventory).

Create a Stores table and add a foreign storeID field to the Inventory table. This way you have a general inventory table where each store can see its own inventory, and that can be summarized/filtered by one or more stores (and other criteria, of course).

vcoctel wrote:

2.-Each sales employee have a commission, based on the amount they sell.

for example.- If she sells under \$1000 she gets the 5%

If she sells between \$1001 and \$2000 she gets the 7%

If she sells over \$ 2001 she gets the 10% commission

My question is how do I can make this calculation for each employee. Do I

first need to add a table of employees?, If yes, do I related them with

invoices?

Yes, you need a staff table; you could use a combination of FileMaker Accounts and Privilege Sets and a self-built login screen.

Now each Purchase/Invoice record could automatically receive the staffID of the logged-in user. Create a commission field to calculate the commission amount, based on the invoice grand total (Make it an auto-enter field so it doesn't have to be recalculated all the time). You can use a relationship from Staff to Invoices via staffID to display the Invoices managed by that staff person, and to calculate the sum of the commissions.

In case you need the formula for the commission calculation:

Let ( [

gt = Invoice:::grandTotal ; // that would probably be before any VAT

c = Case ( gt > 2000 ; 10 ; gt > 1000 ; 7 ; 5 )

] ;

gt / 100 * c

)

Find out how your client plans to pay out these commission; you'll possibly need an additional table of CommissionStatements (with a foreign staffID) so you can track open and payed commissions.

• ###### 2. Re: Need help with inventory related fields

Hi.

1. In general, I'd say if you have multiple stores, a Stores table would be a must to begin with. You also have a Products table. You also need a seprate Inventory table, containing a record for every applicable combination of product code and store ID (a "join" table, satisfying the need for a "many to many" relationship between products and stores).

2. Yes, you should have an Employee table. Each invoice would have the ID of the salesperson who made the sale. At the end of every sales period, you can then search for all invoices of sales made during that period. Sort the list by salesperson ID. Each invoice has a Sale_tot total amount field. Your report would have a Sale_sum field, summarizing Sale_tot. That will at least get you the total sales for each salesperson during the period. You can then create a calculation field to get you the % commission rate, something like:

commission_rate =

Let(

val = GetSummary( Sale_sum; Salesperson_ID );

Case( val > 2000; .1; val > 1000; .07; .05 )

)

and

commission =

GetSummary( Sale_sum; Salesperson_ID ) * Commission_Rate

The report layout must be sorted at least by Salesperson_ID for the GetSummary calculation to work and display properly.

My example, below, also sorts by month name (you'd want to use month number, however).

I also sort by salesperson name and then salesperson ID, so I get both an alphabetical order and the summary working properly.

-- E

• ###### 3. Re: Need help with inventory related fields

ErikWegweiser wrote:

Your report would have a Sale_sum field, summarizing Sale_tot. That will at least get you the total sales for each salesperson during the period. You can then create a calculation field to get you the % commission rate, something like:

commission_rate =

Let(

val = GetSummary( Sale_sum; Salesperson_ID );

Case( val > 2000; .1; val > 1000; .07; .05 )

)

and

commission =

GetSummary( Sale_sum; Salesperson_ID ) * Commission_Rate

I would really try and stay away from trying to calculate this with calculated fields and summaries.  Those are a drag on performance.  Calculate them as part of the workflow and/or update them through server-side routines as soon as the sales data is locked / final / whatever the status is.

Once the sale is final, the data is static so there really is no point in having a report that has to recalculate and re-summarize it every time it is run.

• ###### 4. Re: Need help with inventory related fields

I 2nd Wim's opinion. After building a system that relied heavily on Unstored calcs, it was horribly slow. I changed the unstored calc fields to static fields and I had a lot of fun revamping the system to use Scripts to update the static number fields. Those happen as part on their own over night or as needed throughout the workday.

• ###### 5. Re: Need help with inventory related fields

Hi

I have a problem with the sizes of the presentation, when I put the fields

were I want in the Layout mode, everything is as i want, but when I go to

the final view, many of the fields move their place.

I attached two screen shots so you can see what i'am talking about.

Zury

Discussion* - Visualizza la discussione completa<https://fmdev.filemaker.com/message/146437#146437>

• ###### 6. Re: Need help with inventory related fields

I see that your two images are from different-sized screens. I suspect you have some fields anchored to the right side only, so, when viewed in browse mode with the screen larger than the original layout, those objects slide to the right.

Check the Position tab of the Layout Inspector in layout mode, and adjust the anchors for each field so the fields remain, stretch, or slide as you desire.

• ###### 7. Re: Need help with inventory related fields

Also try 1) selecting the fields in the portal and bring them all forward; 2) nudge them a point or two in different directions. I find FileMaker 13 often gets confused about where objects are (much hair-pulling ensues).