 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / FileMaker Topics / April 2004Passing params between scripts in different DBs (FMP6)
Hi, I want Script Foo in Orders DB to invoke Script Bar in Customers DB and pass it a value. Bar should then pass "the answer" back to Foo. I can't see any way to do this using global fields, and that's the only mechanism I can think of. Is there a general procedure for accomplishing this kind of parameter passing? - - - FWIW, script Foo is processing a new order in Orders DB, and wants to find out if the customer already exists in the Customers DB. Currently the check is based upon telephone number, so it would be possible to define a relationship between the two DBs based on telno, but the matching logic will quickly evolve to some sort of complex fuzzy match, making a relationship impossible. Thanks, Chap > Hi, > [quoted text clipped - 12 lines] > quickly evolve to some sort of complex fuzzy match, making a relationship > impossible. Global fields are the way to go. The step you are missing, in FM 6, is to use "constant relationships" to access global fields in other files. In each file, create a field which is either an auto-enter calc number field, or a calc field with a number result. This field contains a value such as "1" in every record. (if you use the auto-enter method, don't forget to populate all existing records to make this work). Make a relationship between the files using that constant field. Now you can push values into Customers for Script Bar to use, and push the answers back into Orders. Actually, though, you CAN access globals in other files for the purposes of pushing data through INVALID relationships. This actually increases speed since there are no related record IDs to be passed when accessing the relationship. So you can make a relationship between any two fields as long as the righthand field can be indexed, and still access the globals in the other file. It's a bit more advanced concept and won't make much sense until you understand how constant relationships work, which is why I took the time to explain them. Globals are the only way to pass data from Browse Mode to Find mode, so you need to make sure that the globals have the data before you enter Find mode. Lynn Allen www.semiotics.com FM7 Tip of the Day: When showing records from more than one table away, the relationship will fail unless there are valid records in the intermediate tables. Test to make sure such records exist before evaluating calculations or trying to script through those tables. > Actually, though, you CAN access globals in other files for the purposes > of pushing data through INVALID relationships. This actually increases [quoted text clipped - 4 lines] > make much sense until you understand how constant relationships work, > which is why I took the time to explain them. Thanks for taking the time, Lynn. I get it, and it does the trick. Chap > Thanks for taking the time, Lynn. I get it, and it does the trick. While useful (and necessary at times), don't get to carried away with globals and calling remote scripts. I worked on a solution a few weeks back that was a nightmare in this regard. Moving to v7 will be *very* painful for those that take things like this too far. JR > While useful (and necessary at times), don't get to carried away with > globals and calling remote scripts. I worked on a solution a few weeks > back that was a nightmare in this regard. Moving to v7 will be *very* > painful for those that take things like this too far. > > JR How far is too far, for a painless move to v7? I'm a novice at FM and FM scripting (FMP6). The problem I'm working on right now seems to involve moving data between two unrelated databases; in this case from a record in an Orders DB to a new record in a Customer DB. They're unrelated in the formal sense because there's no exact-match key. An order provides a tel.no., name and address, which I can use with a complex algorithm to come up with either a likely match, or a no-match decision. In the latter case I want to create a new customer record containing telno, name, address, and a few other data from the order. Can I write a script in the Order DB that can search and update the Customer DB in this way, without invoking a remote script in Customer DB? I guess that's the first question I should have asked ;-) Thanks, Chap > > While useful (and necessary at times), don't get to carried away with > > globals and calling remote scripts. I worked on a solution a few weeks [quoted text clipped - 17 lines] > DB in this way, without invoking a remote script in Customer DB? I guess > that's the first question I should have asked ;-) Chap, if you're a total newbie at 6, the move to 7 might be easier for you than folks who have years of "6-think" to overcome. However, in this case, what I think you need to reconsider is the operational business rules you're trying to embody in the files. Why are you making orders for customers who aren't in the database in the first place? What if your business rules went like this? "What is the customer name? I should search for that customer FIRST before entering an order. Then, when I create an order, I already have the customer data and can create the related order record properly without this 'working backward' nonsense." A simple re-ordering of rules, of asking for the customer information first before asking for the first order item, would simplify your data structures and your operations. Search on customer name (or phone number, since those are likely unique). If there is no match, ENTER the customer data. THEN make an order, and enter order items. See, your current business processes are a result of migration from paper-based rules. When you write an order on paper, it doesn't matter which lines get filled first. When you do it in a database, it DOES matter, but a quick and simple adaptation will mean far less database design work and far fewer possibilities for error. Databases are far more than fields to be filled in. They also contain business logic, and the design of a system offers many opportunities to examine your current logic, refine and modify it to make sense AND to conform to reasonable data structures in the solution. In fact, I find as a consultant, this is MOST of what I do. For the most part, the fields necessary to hold the data are easily defined in a half hour. Asking all the questions about business rules, processes and logic, and figuring out how to embody the answers in a solution may take hours, days or weeks. Lynn Allen www.semiotics.com FM7 Tip of the Day: In the new FM 7 security system, accounts are for people, Privilege Sets are for groups, and the whole thing is so complicated you should read the Tech Brief at: <http://www.filemaker.com/downloads/pdf/techbrief_security.pdf>. > > > While useful (and necessary at times), don't get to carried away with > > > globals and calling remote scripts. I worked on a solution a few weeks [quoted text clipped - 4 lines] > > > > How far is too far, for a painless move to v7? Thanks Lynn! That was a beautiful response. Chap, Read what she wrote many times over. There are a number of very good messages that will come from it. FWIW... Don't let our local whining about v7 fool you. FM v7 is a beautiful thing. It's what the developer community has been asking for for YEARS. Certainly, there will be a handful of bugs and what not but overall FM7 is light years ahead of its elders. It will lead users to do the "right thing" where v6 et al. requires many hacks to build truly complex systems. Many solutions that have levered those hacks (often because they had no choice) will be difficult to migrate forward. If you're just staring down the road, start in v7 (recognizing you might run into some bugs and server et al. aren't yet available). If you're working an existing system and find yourself wanting to copy things or (to a lesser extent) trigger things remotely step back and ask yourself if you're thinking about the problem correctly. They (the desire to copy et al.) are often indicators that there is a better way. Cheers, JR On 4/28/04 1:18 PM, in article nomail-DD6CE3.11183628042004@netnews.comcast.net, "JR" <nomail@nomail.invalid> wrote: > Thanks Lynn! That was a beautiful response. I'll say it was! Textbook-quality :-) > Chap, > > Read what she wrote many times over. There are a number of very good > messages that will come from it. I will. And I hope my next gig allows me to (a) use FM7, and (b) design from scratch! Thanks, Chap > However, in this case, what I think you need to reconsider is the > operational business rules you're trying to embody in the files. Why are > you making orders for customers who aren't in the database in the first > place? Lynn, Thank you for your lucid remarks! Unfortunately I'm not involved in the design of the entire system - the bulk of it was built long ago, when the company took orders by phone. The company then built an online store which allows customers to place orders over the Web. The site generates orders in the form of emails, which - until now - were printed out and entered manually, like phone orders! My job is simply to read the email orders, batch-fashion, from Entourage, parse them, and automatically create orders in the same phone-orders database with minimal changes to their existing system. It's *ugly*. But re-engineering wasn't an option. I said it was a batch operation - actually it's somewhat interactive. The web orders are first written to a 'staging' data base, after which each must be manually reviewed for possible errors resulting from the fact that the online store is not well-integrated with the rest of the Order placement & fulfillment system. I figured that this would be a good time to handle cross-checking and updating of the Customer DB, since that, also, can require manual inspection. Once the web orders have been through the manual approval process, *then* they are copied automatically from the staging DB to the Orders DB. At this point we know there is a matching Customer record. So I'm stuck with a web-order staging database and a customer data base, which aren't formally related yet. Hence the problem. I guess if I made the web-order email processing a two-pass operation, I could handle customer records on the first pass. But I'd like to have the user review everything about an order at once, for simplicity. (If I were building this from scratch, the online store would be fully integrated with things like the Inventory DB, Customer DB, and Orders DB, and of course there would be none of this email parsing!) Chap > > However, in this case, what I think you need to reconsider is the > > operational business rules you're trying to embody in the files. Why are [quoted text clipped - 31 lines] > records on the first pass. But I'd like to have the user review everything > about an order at once, for simplicity. Ok. I get it now. So in your staging DB, you'll need to present the users with a list of customers who have no EXACT matches in the customer DB. Then present them, on each record in question, with any CLOSE matches, in a portal, based on your complex algorithm. Once they select a REAL match, it pulls in the customer key and kicks that record out of the "look at this" found set. For all records that do not have matches, you must create the customer records. I'd just do a straight import from the remaining found set in the staging DB. After this import, ALL records in the staging file should have matches in the Customer file. Then you can go ahead and create your orders, all with proper matching customer records. Part of the review process is to determine whether there is a customer match, and if not, to make the customer records first, before orders are created. > (If I were building this from scratch, the online store would be fully > integrated with things like the Inventory DB, Customer DB, and Orders DB, > and of course there would be none of this email parsing!) Yeah, email parsing sucks, but you sure do learn a lot about the Position function, don't ya? ;) On 4/28/04 4:47 PM, in article 1gcyjif.bd7em0131olslN%lynn@NOT-semiotics.com, "Lynn allen" <lynn@NOT-semiotics.com> wrote: > Ok. I get it now. So in your staging DB, you'll need to present the > users with a list of customers who have no EXACT matches in the customer > DB. Then present them, on each record in question, with any CLOSE > matches, in a portal, based on your complex algorithm. Once they select > a REAL match, it pulls in the customer key and kicks that record out of > the "look at this" found set. Yeah, sorta pretty much, for purpose of discussion. But going back to JR's earlier point, in which he cautioned against going "too far" with remote scripts and globals, this solution would still require that the staging DB's review script either "communicate" with a fuzzy-matching-algorithm script in the Customer DB, or be able to peruse Customer records directly from the Staging DB's review script. Since I set up a constant relationship between the two DBs (following your suggestion) I guess I can view the entire Customer DB as records related to any particular Staged order record, and roll through them applying the matching algorithm. Is that preferable to using a remote script and passing parms through globals? > Yeah, email parsing sucks, but you sure do learn a lot about the > Position function, don't ya? ;) Heaven forfend! ;-) The parsing is done in perl with regexps. Chap > On 4/28/04 4:47 PM, in article > 1gcyjif.bd7em0131olslN%lynn@NOT-semiotics.com, "Lynn allen" [quoted text clipped - 19 lines] > matching algorithm. Is that preferable to using a remote script and passing > parms through globals? What I was thinking of is a "temporary relationship" based on a multiline key on each side. This would be a calc that would return text like this: Phone number Firstname & " " & Lastname Lastname Company name Left(Address, 10) & Zip with carriage returns between the lines. A multiline key will match ANY line with any single line on the other side of the relationship. So you would see, from Customers, all matching phone numbers, matching first & last names, matching company names or matching addresses. You can have as many lines as you like in a multi-line, up to the 64,000 character limit, so you can get some darn "fuzzy" logic there, depending on the creativity of your calc. Don't forget of course, that some people are going to enter "Comp" one time and "Co" another. :/ Or Bob the first time and Robert the next. This way you get a portal showing all possible matches. No scripting necessary, until one selects a portal line, whereupon a script make the match real by pulling the customer key into the appropriate field to make the actual match. Once the multi-line key is set up in Customers, you actually don't need anything else there. All action takes place in the staging DB thereafter. > > Yeah, email parsing sucks, but you sure do learn a lot about the > > Position function, don't ya? ;) > > Heaven forfend! ;-) The parsing is done in perl with regexps. Oh. Well, that doesn't work so well on the Mac side. ;) Or if you don't know perl. Lynn Allen --- www.semiotics.com 562.938.7890 > But going back to JR's > earlier point, in which he cautioned against going "too far" with remote > scripts and globals, this solution would still require that the staging DB's > review script either "communicate" with a fuzzy-matching-algorithm script in > the Customer DB, or be able to peruse Customer records directly from the > Staging DB's review script. I also said (albeit with less emphasis) that in FM < v7 you may have no choice but to proceed down the path you're on. Comment your design/intent well (on BOTH sides of the "file" fence) so when you have to maintain things 3 years hence the head scratching and "what the...?" thoughts will be minor in nature. JR |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.