11 Replies Latest reply on Dec 3, 2013 2:39 PM by philmodjunk

Working with Portals

Title

Working with Portals

Post

Good morning everyone!  I'm currently working on a how I can multiply 2 fields from 2 different portals, I know its possible but can't seem to get it running.

I have a layout which contains 2 portals Pricing and FieldSales.  The Pricing table that is from another database (SALES) which I connected to a table (FieldSales) in my COSTS database.

The relationship between my two tables are defined as:

FieldSales::ProductNumber = Pricing::ProductNum

AND FieldSales::DayNumber = Pricing::DayNum

I need to compute for a Total Amount which is a field in my Pricing table by multiplying Amount with  Quantity which is in my FieldSales, this is how I wrote the formula:

Total Amount = Amount * FieldSales::Quantity

My problem is that Amount won't compute, is there another way I can do this like using a recurrence of a table or I'm missing something I need to do so that I can multiply Amount with Quantity?

Thanks everyone, any ideas/suggestions is deeply appreciated!

• 1. Re: Working with Portals

Asking the obvious.  Are all referenced fields numbers, and calculation result a number?

• 2. Re: Working with Portals

Yes, the fields are of type number and Total Amount is a calculation that returns a number as result.

• 3. Re: Working with Portals

Also checking the obvious:

Which is the exact expression in the Specify calculation dialog?

Total Amount = Amount * FieldSales::Quantity

or

Amount * FieldSales::Quantity

• 4. Re: Working with Portals

And how many records do you have in FieldSales with the same daynumber and product number values?

• 5. Re: Working with Portals

Dran Lang:

Thank you for your post.

Along the same basic questioning, you had the following formula:

Total Amount = Amount * FieldSales::Quantity

Making sure you are not referencing an "Amount" field in the current table, the calculation should read:

Total Amount = Pricing::Amount * FieldSales::Quantity

In the related tables, can you verify Pricing::Amount and FieldSales::Quantity are not blank?

TSGal
FileMaker, Inc.

• 6. Re: Working with Portals

Amount * FieldSales::Quantity

Currently there are 35 records with the same DayNumber and ProductNumber.

• 7. Re: Working with Portals

And in your calculation, FieldSales::Quantity will only refer to Quantity from the first related record, not the sum of Quantity from all related records.

I'd guess that you really need:

Amount * Sum ( FieldSales::Quantity )

• 8. Re: Working with Portals

Thanks Phil, I got it now.  I do have a similar question but this has a twist in it.  I just got a off a meeting after lunch and my boss is asking that I come up with a way to have our PriceList table which is from a different database (SALES) to be able to see it in our COSTS database.

The way my boss wants it to work is we have a PriceList number that we can always use to identify a PriceList and tie it with customer orders.  So, there's a CustomerNumber and a PriceList number to identify it in our FieldSales table.  I got the part which shows the customer's info showing together with the PriceList.

The PriceList table contains ItemDescripition as a text field, UnitQty field which is of type number, UnitPrice as number field and Total Price as calculation field of type number.

ItemDescription contains the items and UnitPrice which are the prices for each items in the ItemDescription already selected and is assigned a PriceList number in the SALES database.

PriceList number can be assigned to any customers that orders the same set of items and when a new day is added to the report the ItemDescription and UnitPrice doesn't change but the UnitQty and the TotalPrice will be empty and waiting for the entry of UnitQty.

How can I create a new day for orders without the ItemDescription and Price changing?  Can I use a recurrence of my table, what relationship should I define for it?

• 9. Re: Working with Portals

I'm not sure that I follow all that. So priceList is a table of unit prices where an entire set of such records can be linked to any given customer?

Why would a Quantity (Qty) be defined in a table of prices?

Normally you have one table to list the unit prices and another table where you select items and specify a quantity that looks up (copies) the unit prices from the first table and which becomes part of a sales order or invoice. The typical tables and relationships for that can be found in the Invoices starter solution and look like this:

Invoices---<Invoice Data>-----Products

Where Items purchased and quantities are selected/entered in Invoice Data and unit prices are looked up from Products.

But that doesn't seem to quite fit what you are describing here.

• 10. Re: Working with Portals

I originally did the same as what you pointed out regarding the use of one table but my boss who did the proposal with our sales and accounting department turned it down since he said that a 3rd party accounting software/suite will handle data/records that we are going to import from filemaker.  They wanted me to have/find a way on how we can attain the same result but using a different approach and that is by having the unit prices in another table.

• 11. Re: Working with Portals

Sorry, but your boss shouldn't be designing your data model. What they see and use on the screen and what you have set up in Manage | Database are two different things.

But I'm not saying that what they have is wrong or don't work. I'm just noting that it's not set up like I'd expect and am having trouble figuring out from your description how it is supposed to work.