Duplicate values across multiple fields

[jdu98a]

I have a table of all the different shipping options my company provides and the amounts we charge for those methods.

Here is my issue. A portion of our sales invoices are downloaded each day from Paypal. The comma-delimited file that Paypal provides lists only the shipping amount charged for the method selected by the customer. There is no option to see the actual name of the method selected. As a result when downloading these sales Filemaker must look at the shipping charge that is downloaded and correlate that amount with the correct shipping method from my shipping_methods table. It's a backwards way of doing it, I know, but with Paypal there is no other option. In order for this to work correctly every tier (seperated by weight) of every ship method must have a unique value.

This isn't a big deal really except when it comes to changing my shipping prices. In order to be sure that I am not duplicating any prices I have to export the table to an excel file that uses conditional formatting to highlight any values that are not unique. Then when I have my prices set, I have to reimport them back into Filemaker.

Is there any way to get Filemaker to find these duplicate values for me. I am trying to avoid having to create a huge number of relationships and/or writing a lengthy script to do a comparison of each and every combination of price fields. Thank you in advance for any help.

If needed I can email the excel file I use that may make things a little more clear.

Edited November 20, 2008 by Guest

[Kevin Frank]

Is there any way to get Filemaker to find these duplicate values for me.

Searching on ! will find duplicate values.

If the combination of several fields needs to be unique, create a concatenated calc field and search that field for !

E.g., a calculated field (text result)

weight & " " & shippingCost

HTH,

Kevin

[Robert Schaub]

Here's an example

I did, In fact the filename is exactly like your post name. http://starrdata.com/downloads.htm

Only problem is it require 9 or higher. Your profile say FM 8

[LaRetta]

In order for this to work correctly every tier (seperated by weight) of every ship method must have a unique value.

It sounds like you want to change (raise or lower) these rates (in your Shipping_Methods table) one at a time but you want to be warned if your change means you have a duplicate (meaning your PayPal method interpretation would fail), and you want to know in that moment. If so, I might suggest creating a new text field with an Auto-Enter (Replace) of: Weight & " " & Dollars. Then set this field's validation to Unique Value.

In this way, incoming PayPal shipments can auto-enter your Ship Method (depending upon weight and dollar) but you can manually add or change rates in your Ship_Methods table and you will be warned if your entry is not unique.

LaRetta

Edited November 21, 2008 by Guest
Corrected to weight & dollars which is, I think, what is meant here

[LaRetta]

Ah heck, wait a minute. If all you know is the dollar amount from PayPal, how do you know the weight or method? I would think you need two of the three ingredients to determine anything!? If weight doesn't matter, just put the unique validation on the dollar amount in your Shipping_Methods file.

The comma-delimited file that Paypal provides lists only the shipping amount charged for the method selected by the customer.

I'm now confused on how you determine the method with only the dollars - don't you receive the weight from PayPal as well? And don't you plant the Method into the Sales Record in your table once it is looked up? It should be because otherwise, down the road when rates change, it will show an incorrect ship method!

Edited November 21, 2008 by Guest

[jdu98a]

Well, now I'm going to have to explain things deeper than I intended, ha. But that's ok if it helps.

Each record in my ship_methods table looks like this:

Field names

method_name

tier_1_high (this is the high weight limit for the tier one pricing bracket)

tier_1_low (this is the low weight limit for the tier one pricing bracket)

tier_1_price

tier_2_high

tier_2_low

tier_2_price

tier_3_high

tier_3_low

tier_3_price

... and so on to a total of 5 tiers of pricing

So, each shipping method has 5 different prices; one for each tier. Under normal operation my invoice table looks at the method selected, calculates the total weight of the product being shipped, and finds the corresponding tier to give me the price. My shipping price field is an auto enter field so I am able to change shipping prices and not effect old invoices.

Now lets look at Paypal. The shopping cart on our website calculates product weight and shipping price with the same tiers as the database. However, the file downloaded from Paypal only supplies us with the end result of this calculation -> the final shipping price. When this file is downloaded into my invoices table the import script reverses the normal method of calculating shipping by importing the price into a shipping_price_import field that (when it contains a value) overrides the normal calculation. In this way it doesn't actually matter which shipping method is selected. The imported shipping price will be accurate to what the customer actually paid. However, it is important, for obvious reasons, that the correct shipping method is selected. So as part of my import script Filemaker takes that downloaded shipping price and searches in my shipping_methods table for the method that contains the matching value (in whatever tier it might be). It then assigns that method to the invoice.

This all works great as long as when we change our prices we do it both in Filemaker and in our website shopping cart on the same day. That way the following day's download will sync up correctly. And shipping prices can be changed at any time without messing up any old invoices.

As you can see, however, this requires every single tier of every single shipping method to contain a unique value. And this is what I need help in accomplishing short of exporting to Excel like I do now.

I hope that is all clear.

Edited November 21, 2008 by Guest
just fixed some typos

[jdu98a]

Chopper,

I currently am designing in 8 advanced, but I have a copy of 9 so I'll take a look at your file and see what I can get out of it. Thanks

[comment]

This is not a good arrangement to have. I am referring to your basic method, even before getting to the PayPal issue. There should be a separate RECORD for each shipping method/weight bracket combination. That will make a lot of things easier - including this.

I am still not clear on whether you know the weight when you search for the shipping method. Assuming you do, then the requirement to have only unique prices within each weight bracket is easily enforced as shown by LaRetta.

If instead of validation you prefer to flag the duplicates, define a self-join matching on bracket AND price, and count the related records. You could actually do the same thing with your current structure - but it would require 5 self-join relationships.

[jdu98a]

You could actually do the same thing with your current structure - but it would require 5 self-join relationships.

yeah, this is the only way I could think to do it, and it is something I am trying to avoid.

I like the idea of individual records for each method/bracket. I can see how that would more or less alleviate my issue as well. It's a change I will put on my long list of improvements to make, ha. Thanks for all the advice.

I am still not clear on whether you know the weight when you search for the shipping method.

The weight is known when entering an invoice by hand, but it is NOT known (at least not by Filemaker) when downloading/importing from Paypal.

Edited November 21, 2008 by Guest