3 Replies Latest reply on Jul 6, 2010 11:54 PM by alessandroconti

    Question about cross-indexing



      Question about cross-indexing


      Hi, sorry to post such a newbie question... I'm using FMP11

      Let's say I have a table for shipping charges, three fields and a bunch of records, let's say:

      Object name/Shipping charge within UK/Shipping charge worldwide




      Now I'd like to create a form/table/portal/whatever to cross-index my customer's needs, so that I insert the object he wants and where he lives and I get the shipping charge.

      Sorry again for the naive question, but I don't have a clue about what to look for. Thank you.


        • 1. Re: Question about cross-indexing

          Shipping would be a field based on location using a case statement

          Location would be UK or WW  Case Location equals UK sets Shipping to UK shipping charge, etc

          Case Statement


          • 2. Re: Question about cross-indexing

            I see two possibilities based on your example numbers.

            With numbers you posted, your worldwide shipping charge is always twice the UK rate. If that is always true for all objects, you can simply look up and total the UK rate for all objects and double it for customers outside the UK. If this is not the case, you'll need to set up a calculation field that selects between the two shipping rates for each object.

            Have you set this up with the typical Invoice, LineItems and Products tables so that LineItems lists the objects on the Invoices layout and looks up prices and shipping rates from the Products table?

            If so, this calculation field could be added to LineItems to select the correct shipping rate:
            If ( Invoice::ShipToCountry = "UK" ; UKShipRate ; WorldShipRate )
            UKShipRate and WorldShipRate are LineItems fields with values that would be looked up from Products.

            • 3. Re: Question about cross-indexing


              I didn't manage to use the IF nor the CASE operators, as I was working with two tables and that seemed to interfer with the logical association of record+record. 

              However I used a stratagem creating a double association so that 

              object = object

              and shiptocountry = shiptocountry

              This way if mr. Brown purchased a computer and wants it shipped to UK, we have an Object and a Shiptocountry that will individuate one and only one record in the Shipping rate table, and this is my need. 

              Thanks to both for help!