4 Replies Latest reply on Feb 13, 2014 9:04 AM by philmodjunk

Calculation and Auto Enter problems (VLOOKUP)

Title

Calculation and Auto Enter problems (VLOOKUP)

Post

I'm trying to set up a purchase order system in FM Pro 12, I have three criteria that needs to be fulfilled when calculating the value, this is based on roles, e.g.

This is the layout I have:

Product1 Description1 Day1 Fees1 Role %1 Role1 Total1

Product2 Description2 Day2 Fees2 Role %2 Role2 Total2

Product3 Description3 Day3 Fees3 Role %3 Role3 Total3

Product4 Description4 Day4 Fees4 Role %4 Role4 Total4

Product5 Description5 Day5 Fees5 Role %5 Role5 Total5

Product6 Description6 Day6 Fees6 Role %6 Role6 Total6

Product7 Description7 Day7 Fees7 Role %7 Role7 Total7

Product, Description, Day and Role are all drop down menus,

Fees is entered manually

Role% should come from another database once the role is specified, although this only seems to work in Role%1 not on any of the other lines??? • Once the role% is auto entered the total of the fees is calculated, again this seems to be calculating it for the first line then entering it in to all the total fields 1-7???.

Some roles need to have a fixed fee entered as opposed to the % being calculated, I had this working when using repeating fields but it doesn’t seem to work now even though I’ve updated the calculation:

Original Calculation:

Case(Role::Role %[1] = "Fixed";200;Role::Role %[1] = "AB";100;Fees[1] * Role::Role %[1])

Updated Calculation:

Case(Role::Role %1 = "Fixed";200;Role::Role %1 = "AB";100;Fees1 * Role::Role %1)

Plus only the first 2 roles work from the drop down menu, none of the others put the % in?

I hope I’ve explained it so people can understand, but please ask additional questions if I’ve missed stuff. Many thanks in advance for any help, it is greatly appreciated.

Kind regards

Nick

• 1. Re: Calculation and Auto Enter problems (VLOOKUP)

The problem is that you have set up multiple fields for the same type of data in one record. In order to look up the data, you'd need to define a different relationship for the look up for each set of separate fields as each specifies a different match field. Some people encounter the same issue when they make the mistake of using a repeating field where they then get a value to look up only in the first repetition of the field.

A better approach that eliminates the need for multiple relationships to different table occurrences of the same look up table is to replace this set of fields with a portal to a related table. That makes each set of fields the same set of fields in a different related record. The result on your layout can look much the same but now you can set up a single relationship from this new related table to your look up table in order to look up the needed data.

You can see a working example of this in the Invoices starter solutions that come with FileMaker 11-12. The invoices layouts in these solutions use a portal to a line items or invoice data table--which in turn looks up pricing info from a Products table.

• 2. Re: Calculation and Auto Enter problems (VLOOKUP)

Hi PhilModJunk,

Thanks for the advice, I think I need to look at it more closely, I've created a new database using the Invoice template as you mentioned above, I foresee some major rewriting taking place to get this all sorted.

N

• 3. Re: Calculation and Auto Enter problems (VLOOKUP)

Hi Phil,

I've set up a portal as best I could looking at the invoices file, I can't figure out how to get a new line or why the grand total is multiplying the total by the number of lines, e.g. I have the portal set to seven lines, I have 100 in the total and the grand total shows 700, but I can't figure out how to get a new line to enter additional products?

Many thanks

N

• 4. Re: Calculation and Auto Enter problems (VLOOKUP)

Without a more detailed description of what you have set up, I can't provide specific assistance.

There are two ways to make it possible to add new records to your portal:

Go to Manage | Database | Relationships, double click the relationship line linking the portal's table occurrence to the layout's. Select "Allow creation of records via this relationship". This causes a blank "add" row to appear in your portal after the last portal entry and you can add new records just by entering data in the portal row.

Put a button on your layout that performs a script to add the new record.