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

    Working with Portals

    DranLang

      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
          SteveMartino

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

          • 2. Re: Working with Portals
            DranLang

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

            • 3. Re: Working with Portals
              philmodjunk

                   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
                philmodjunk

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

                • 5. Re: Working with Portals
                  TSGal

                       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
                    DranLang

                         Amount * FieldSales::Quantity

                          

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

                    • 7. Re: Working with Portals
                      philmodjunk

                           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
                        DranLang

                             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
                          philmodjunk

                               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
                            DranLang

                                 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
                              philmodjunk

                                   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.