Jump to content

Adam S

Members
  • Content count

    18
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Adam S

  • Rank
    novice

Profile Information

  • Gender
    Not Telling
  1. Hi, everyone. I was just about to post the question below and happened upon a solution just before clicking go. Though perhaps not traditional, I thought I'd post the question and resolution in case it's helpful to someone else searching in future. The quick summary is that an importing problem in FM 11 was solved by updating my ODBC driver. Here are the details: Just switching an old file from 8.5 to 11 today and hitting a small roadblock. The file includes a script with an import step pulling data from Postgres (not using ESS as it's not yet implemented for Postgres). Some of the fields being imported are "money" (AKA currency) field types on Postgres, importing into number fields in Filemaker. In v. 8.5 the import works successfully. In v. 11, however, "money" fields that include a $ dollar sign are importing with a ? question mark value. While we prefer to keep these as number fields on Filemaker we tried changing the field type to text and got the same result. We also tried setting the field definition to auto-enter a calculated value of getasnumber(item_price) and even Substitute( item_price ; "$" ; "") (and for those tests we did uncheck the "Do not replace existing value" box). Any ideas or existing threads that might address this please? Feeling like I'm missing something obvious here. Thanks for your help. SOLUTION: Just as I was about to post I realized I should include the ODBC driver type and version I was using (which was ActualTech ODBC Driver for Open Source Databases v. 2.9x). Thinking that might be a bit out of date I updated it to version 3.0.14 and the data seems to import correctly now. It seems the driver's translation may have been updated as Postgres data types were updated. Hope this is helpful to someone.
  2. Adam S

    Get shipping rates in FMP?

    Hi, Jason- Check out Scriptmaster for this (http://www.360works.com/scriptmaster/). The basic implementation is free. You add some files, build a plugin or just have the Scriptmaster file load first and close each time you launch, and then there are a bunch of extension utilities available. We've set it up to query rates when we click a button triggering a script with a "Show custom dialog" step pulling the info from FedEx. Jesse has been good at making changes that we've requested to fill out some features (such as adding a switch for residential vs. commercial). Note it does NOT yet show rates for SmartPost and hopefully it will do so someday (add your voice to this). If you need some sample code from that script please ping us (see various e-mail addresses at http://www.thepurplestore.com/contact) and we'd be glad to send you what we've got. If anyone else wants to co-develop some filemaker actual Mac / Filemaker /FedEx label printing functionality together we're thinking this could be done cheaper than licensing software from outside.... Hope this helps! - Adam
  3. Matt- We're hoping that Filemaker will finally adopt ESS for Postgres. We know so many companies that are switching from MySQL to Postgres these days that maybe the odds are better for Filemaker 11 to at long last support it directly. It's been a really, really long wait and maybe there's a glimmer of hope at last. ActualTech's ODBC driver is great for at least some connectivity and their staff are helpful, but it's not a live link and updating from Postgres requires an import step and its resultant delay. If having Postgres and Filemaker speak directly via ESS is important to you, please let Filemaker know about it here: http://www.filemaker.com/company/contact/feature_request.html
  4. Adam S

    FedEx GetRates Function

    Jesse- Sent e-mail with the ScriptMaster file as requested. Just wanted to follow up here so we can post results for anyone else who might have a similar issue in future. Thank you.
  5. Adam S

    FedEx GetRates Function

    Just wanted to bump this and see if anyone can please help. Jesse- are you out there monitoring this by chance?
  6. Adam S

    FedEx GetRates Function

    Hi. We're running the latest Scriptmaster version in Filemaker and are trying to get FedEx Get Rates to work. Running from within the module itself for testing, we're getting the included test variables to return a result, as will our FedEx test environment credentials. With our real production credentials, however, we're getting zero as the result. For context, we just obtained the production credentials about an hour ago. We copied and pasted to avoid typos and we're pretty sure we've got the information in there correctly (we of course could be wrong). We're wondering: a) how to get this working as-is? why this script points to 360works' website instead of FedEx and if we can/should just change the URL to point to Fedex directly for security and such. According to our FedEx tech contact, Web Services production requests should be going to https://gateway.fedex.com:443/web-services (assuming one is using web services version 3, as this script does). Is there something translated and adding value in the redirection? We tried dropping that URL (naively) in the script at the new URL step it returns a 500 error. Any help would be appreciated! Thank you. - Adam
  7. Adam S

    SQL Features

    It looks like this was an old post that didn't get answered, and it's a topic near to our hearts, so I'll go ahead and cap this topic off for posterity. Unfortunately, no, Filemaker, even at v. 10, does not yet support Postgres (PostgresQL) with its relatively new ESS feature. One can still hook in via ODBC using Actual's driver (and Actual staff have been great about helping with this). Well worth the price. Still, synchronizing by script and importing data back isn't the same as just having the data synced up, and we have been not-so-patiently waiting for Filemaker to add full ESS support for Postgres. It looks like the Postgres, which we strongly recommend as a powerhouse database to run websites and corporate solutions, has been gaining a bit of user base (especially with the shakeup of MySql buyout lately), so hopefully people will help pressure FM to get ESS for Postgres added! *Crosses fingers*
  8. Right. I was hopeful there was some elegant way to solve the puzzle without using a global in this way. So far I can't see a way for a portal to hold calculated values that will dynamically update in this way if those values hinge on the record being browsed in the parent table.
  9. Thanks, Comment. Our structure is approx. what you described, being Customers -< Order_Data -< Addresses. We have the portal set up showing any previous addresses that match the customer for a given, new order. It sounds like the only way to go so far, barring someone's magical idea, is as you described, having a script set a global in the addresses table to say "link to this specific order_data record now," base a relationship on that global, then have the comparison formula in addresses refer to that relationship. We have this set up (and working) using the order number field in order_data as the foreign key: addresses g_order_number_external_to_relate (set by script) maps to order_data order_number_external (unique) The scoring calc field can now compare each address component in fields in addresses to the buyer and ship-to addresses in order_data and build a score. This approach needs the global g_order_number_external_to_relate in addresses to be reset each time an order is reviewed, so it knows which addresses to compare. Without script triggers (we're running this in 8.5), the user will have to hit a button to set these global values; even with script triggers, it seems like extra resource use to run a script each time a field is entered, but I digress. It turns out not to be the biggest deal for this use since our staff are usually transferring addresses as part of a larger script, so we can set the global there when it gets to each order. Ideally, though, it would be more useful for this and other applications to use FM's awareness of which record the user is browsing (within order_data) to guide the resulting address score in each portal row. The reason we thought this might be doable without setting globals was that a) we only care about this value in the context of the order_data table (so we don't need an address match calc to have a valid value when browsing addresses, but only when browsing through the order_data entries) it seems the order_data table could already be aware of which record we want compared because it knows which record is being browsed, and it has a portal showing candidate addresses so it knows which we'd like to score. It just seems like all the information is sitting right there and FM would have some way to piece this together. I was hoping the calc field in addresses could somehow, in context of the portal, realize they should be calculating relative to the current order_data's record (even if they were blank or not useful if browsing addresses itself). We were also hoping to do it without scripts so they'd be dynamic as we moved records with no extra step, but I'm not sure that's possible. BTW, I was not intending to be vague on how the addresses are scored, just hoping to be clear that our question was not about address matching heuristics; that's a whole other rabbit hole! If anyone else (LaRetta, guessing you're still swamped, yes?) has a suggestion on how to do this absent setting a global each time one enters a record, it would be great to hear. Otherwise this might be one of those "can't be done" kinds of things. Thank you, all.
  10. Hoping someone might have a few minutes to help with this question now that July 4th is over perhaps? We're still stuck on this and would like to get the new system implemented before we train some new staff. Thanks!
  11. 1. Thanks to Bruce for helping us focus a bit on the orig. question. Other input is much appreciated, though. 2. I'm not the world's normalization expert, but the addresses really do belong in a separate table when going beyond a basic contact list. We began with a customer's main address (often billing) and their one alternative as part of the customers table. That works until they want to ship to a work address (now we're at three sets of address fields), send a gift to their sister (now we're a four), etc. The table size and efficiency starts to diminish. I so wish we'd started with a separate addresses table in the first place. 3. Back to the relationship q. Bruce, your idea sounds good though I think it hits at our original snag, which is how to show a score (whether textual or numeric) of how well an address matches. We're able to set this up showing only 100% matches (trimmed and such), or having separate relationships for each aspect (first name matches, full name matches). We have a draft calc. field with a decent placeholder matching formula and cases ready to go (it's in address_match_rating_payer in order_data if you want to see it, and eventually we'll add a bit more heuristics). During order transfer, there does seem to be genuine need to see both FM's ideas of close matches as well as all addresses connected to that customer. The heuristics will never be perfect and the user should see all addresses to avoid duplicates and also allow us to add a new info, such as a new phone number, arriving with the order. My hope is to include this directly in the portal relating to all customer addresses. It might, though, require a separate relationship and I think it might bring up the portal-within-a-portal impossibility. Hopefully there's a workaround, please? If it can't go in the portal, maybe a (repeating?) field goes next to each of two the new addresses that identifies matching address_ids with their match "score." It seems less useful than putting this info in the portal with each of the existing addresses, but it's a possibility. Thoughts appreciated whenever someone has a moment. Thank you.
  12. LaRetta- Hopefully it's workable but here's why you're seeing what you're seeing. This solution started with the Customers table storing addresses directly in the customer record where it shouldn't have (with two sets of address fields, buyer and shipping); the ShippingDest address fields there are legacy and can be ignored, please, as I forgot to remove them before posting. This originally matched a setup created in another database and this is, at last, our process of creating a proper, relational approach. The addresses table intentionally contains the name associated with a given address. For a billing address someone may go by a legal name but for shipping they often choose a different name. Same for gift addresses, so if it's to a friend, there's a different name. We are, for the moment, also including the phone #s received for that address in that table until we sort out how best to track them; we often need the number tied to a given address, such as the gift recipient's number to provide to FedEx. If you've got a better suggestion on that aspect we'd love to hear it, but hopefully it won't get in the way of seeing something for the address matching question. For address matching, the goal is to focus on seeing how well the order_data transfer fields match existing records in the addresses table, such as how well the field order_data::transfer_buyer_address_street matches any existing address_street entries in addresses. If this can be done via a calc field, our question is which table should contain it, how to build the relationship, and if it can be retrieved and displayed inside a portal within order_data showing all addresses associated with a given customer. That make sense? Thank you.
  13. As requested, here's an attached sample (zipped) of three files with the relevant, related tables. They should all open with guest access. LaRetta and others, please let me know if there are any questions on these. Thanks! Relationship_Customer_Match_Example.zip
  14. I'll try and switch gears to see if I can create a thinned sample file; not sure how that will go, but will try ASAP. In the meantime.... The transfer holding fields for address are broken into separate component fields (first name, middle, last, street_address_1, street_address_2, etc.). There are two addresses received with each order, one for payer and one for ship-to. If they're deemed new by the user, they're created as real address records (in our addresses table) during the order transfer process. A typical field name would be order_data::transfer_shipto_address_street2, which gets transferred (and compared) to addresses::address_street2. We considered a calc. field that concatenates the entire address and compares them, building a relationship just on that. It would only check for a precise match though and would miss opportunities to match those that are substantially the same (e.g. apt. number on line one vs. line 2 of an address) and to point the user toward differences to be checked. Please let me know if there are other questions in the meantime. Thanks in advance.
  15. Thanks, LaRetta. That might even have simplified another step in this process. Can I gently nudge this topic back to the original question and, if you or others have a moment, would you please chime in on that?
×

Important Information

By using this site, you agree to our Terms of Use.