pcourterelle

Hi folks...FM 6...developing a contract / client /production database solution for a local publisher and have been able to implement the SM to a farily large degree and with good success. The business publishes multiple publications. The db is in use but there is one area I'm having difficulting with is searching contract data. Three essential elements at play: Clients (1 Client) Contracts (Can have multiple contracts linked to Clients via ClientID key) Ads/Production Info (And hundreds of ads linked to Contracts by unique Contract Numbers ) Data files: Clients (ClientMan), Contract (ConMan), Ad/Production Ordering (ProMan) one user interface file called AdManager (AdMan ) To search clients and ad/production info I've used filters and portals to show a short list of possible answers from which the user then selects to view or edit. But I have not yet come up for a solution to search for a list of contracts between specific dates or by a specific contract number. Thoughts? I'm sure there's a solution but I can't envision it at the moment... thnx phil courterelle

First, it seems like you're recreating the wheel here. If you do any online banking you can access, print and/or email exactly this type of information in the format you describe with the online tools the banks supply. It seems that the simplest solution is to forward information direct from your bank to your accountant rather than create a db to track info that the bank is already providing you. Some banks even allow you to download the info for use in spreadsheets. Be that as it may.... you asked: Is it better to have a separate table for each bank account, which would include both credit and debit records for that account? No. This simply complicates matters and makes entering your data a pain. Unnecessary duplication. you asked: or Should I have everything in a single table, all bank accounts and all debits and credits? Yes, this is how I think it should be done and how I would approach it. You create a unique identifier for each bank account, as well as fields for debits, credits, summary calculations to calculate your openning and closing balances. This will allow you to create searches, tables and reports to satisfy you accountant's need for "double entry" accounting. you asked: Should I use just 2 tables : i.e. all the credits, from all 5 bank accounts, in one table and all the debits, from all 5 bank accounts, in another? Certainly workable but not as streamlined. Question: why is your accountant having you do all the "double entry" accounting stuff? Isn't this what your paying the accountant to do? Just curious. Cheers pc

Great ideas all...thanks phil courterelle calgary

hi...once in a whiloe when I'm testing script with a loop I'll goof and miss a step and create infinite loop, one that has no end. Is there a way to halt or exit the loop with keyboard commands? When this occurs I have to reboot the Mac to halt the loop. thanks in advance. pcourterelle

Is there a standard development approach to layouts for dbs? Normally when I design dbs I design layouts to go with each db. For example, on a client db there would be layouts for entering new clients, searching, displaying search results, editing single records and the like. This is a typical FM approach, at least insofar as the books are concerned. Some layouts would have self-joins and/or other relationships and portals other would have subsummary and trailing summary reports. Essentially if the layout items were of direct concern to the information stored in the db it would stay with the db. I've begun to question the logic of this approach. I wonder if it would be better for the user to have one file strictly for layouts and would contain all layouts for the entire solution (ie clients, sales, ordering, invoicing) and use relationships and scripts to connect the relevant dbs. In essence, using the above example, there would be a fifth db called user_interface. The user would never have to jump to different dbs/tables but would stay in the same db all the time. Thoughts? Pros, cons? Is this stating the obvious? thanks phil courterelle

Tom/Vaughn: Thanks for all your input...sorry for taking so long to get back to this but was on a mini-holiday for a few days...The exceptions table would look something like this: Record Month Year Subtraction 1 July 2004 -1 2 Dec 2004 -1 3 July 2005 -1 4 Dec 2005 -2 But what relates the exceptions table to the AdOrdering db? Year? Month & Year? I just don't see it. A contract may or may not include July or Dec therefore I need a way of identifiying when these months come into play. In addition a contract may start in one year and finish in another. For example, Nov 2004 to August 2005, including Dec 2004 and July 2005. This is what I mean by hard coding a solution...a caluclation that takes these factors into consideration... cheers pc

Vaughn: I had thought of this approach but the number of possible publishing dates per month changes every year. For example: In 2004, July has only two possible publication dates while in 2008 it has three. This means I would need to hard code into the db every exclusion for every year for every product. No?? But I agree with you that I need to isolate the exceptions. I'm considering the following: Sum of if start date is less than July, then take ((June 30 - start date)/14) +1 to calculate all the biweekly periods between the start date and June 30) Plus 1 for July If end date is greater than or equal to August, than ((end date - August)/14)+1) for all biweekly periods from Aug to the end date. End Sum The problem with this is that the start date, July, August and end date may be in different years. Each contract is only one year long but can start at any point. For example: start date 10/10/2004. end date 9/30/2005. As for flexibility...they want to be able to bend over backwards, tie it in a bow, tie the bow into a pretzle and tie the preztle into a Gordian knot.

Fenton: Thanks for the downloads but unfortunately I only have FM 6 so I could not view your examples I wasn't able to get the portal to filter properly based on the client id. What I did instead was create a calculated field in the client db that truncated the client's name to two letters. Thus Albertson became Al. I created a field in the sales db to enter a client's name and truncated it to two letters as well. I then created a portal based on the truncated names. While in the Sales db you can enter the name Allison and it will show you every name that begins with Al...Albertson, Als, Alecs and so on...the user can then select from the name. At the bottom of the portal I give the user the option to enter a brand new name into the client db if the one they are looking for does not already exist. This is not quite what you suggested but so far it works...If the client db becomes too large I will have to change the truncation to three or four letters . thoughts?? pc

No problem...Sorry if the post is confusing....I've listed the dates for every issue in 2004 and the beginning of 2005. Note that the issue dates are a running total. The magazine publishes every two weeks EXCEPT in July and December when it publishes only once in each month. From 1/8/2004 to 7/8/2004 the pattern is an issue every two weeks. The Issue for 7/22/2004 is skipped as they only publish once in July. Same for December. The specific dates where they skip varies depending on the year (obviously) as does the number of issues skipped. The formula Round((((Today's Date - OriginalIssueDate)/14 )+1),0) calculates the total number of bi-weekly (14 day) periods between the original issue date (1/8/2004) and the current date. But this does not account for the exceptions in July and December to the biweekly rule. Date -- Issue 1/8/04 -- 1 1/22/04 -- 2 2/5/04 -- 3 2/19/04 -- 4 3/4/04 5 -- 5 3/18/04 -- 6 4/1/04 -- 7 4/15/04 -- 8 4/29/04 -- 9 5/13/04 -- 10 5/27/04 -- 11 6/10/04 -- 12 6/24/04 -- 13 7/8/04 -- 14 ------------- only one issue in July 8/5/04 -- 15 8/19/04 -- 16 9/2/04 -- 17 9/16/04 -- 18 9/30/04 -- 19 10/14/04 -- 20 10/28/04 -- 21 11/11/04 -- 22 11/25/04 -- 23 12/9/04 -- 24 ---- 12/23/2004 is skipped 1/6/05 -- 25 1/20/05 -- 26 2/3/05 -- 27

I am working on a db for a small publishing firm. The company produces two biweekly magazines (that is each magazine publishes on alternating two week cycles.) Publishing day is always Thursday. For most months the magazine is published twice but there are months during the year where they will publish have to publish three. (ie there are two months every year that have five thursdays, three of which could be publishing dates). The first publication came out Jan 1, 2004. Thus Issue one was in the second week of 2004, Issue 2 was in week 5, issue 3 in week 6 ect. Each magazine has an Issue tag (issue 1, issue 2, issue 3...issue N) I need to be able to calculate the issue from the current date. If the magazine published every two weeks like clock work this would be simpler but there are two notable exceptions. The company only publishes the magazines once during July and December, which throws off the issue count. Also the Issue numbers runs consecutively. Thus the last issue of 2004 will be 24 and the first issue of 2005 will be 26 and so on. I cannot wrap my head around the exceptions and how to account for only one issue in July and Dec and how this affects the running issue count. A good chunk of the database depends on this calculation being 100% correct. After banging my head for two weeks I've come for help. Anything you could provide would be greatly appreciated. thanks phil courterelle calgary

Basically two dbs...ad ordering, production related via a ad num that is unique to each ad. This is a parent-child relationship where the production db is dependent on the ad ordering db for primary data. Eventually I did use setfield in a script to copy production related data (size of the ad, type of ad, where it's coming from, how the arriving and what the client wants done for proofs) from the ad ordering db. This seems clunky to me and poor design. Is it? For each new issue the ad sales staff will enter new production info and a new record in the production db will be created. If they change info then the info will be copied into the existing production record. pc

Fenton: thanks for the input, this sounds like a reasonable alternative. When you say "no IDs show, but that is what is set by the script" do you mean that the relationship is based on the client id rather than the client name? I assume this is what you mean. Having a trap built into the interface makes sense.

Whenever I design a db that requires relationships of the model sales/client where sales and client info are in separate tables I run into a user-interface problem. Which is the most efficient way to set it up so that the db user can easily access potentially 10,000 clients to enter sales info? That is, when a sale is entered into the sales db the db user must either create a new client or select from a list of current clients. From a pure db design perspective unique client ids work best but then each db user must know the id for every client. Telephone numbers work but are not stable and a single client can have multiple telephone numbers. A find function can work but the db user must perform a find client for every sale. The find can be based on name, telephone or other characteristics. For smaller dbs I've also tried using a pop-up menu that displays the name of each client and the user can select from the list. The question, is there a better way? thanks phil courterelle calgary

For this solution why is a script not workable? I have a similar challenge where data from one db needs to be included in another db and a simple relationship or lookup isn't the answer. I have one db that stores contract info and another production info. Ad sales ppl input data into the contract db, including info for production. I have an 'enter' button at the bottom of the page which accesses a script that copies the production info into the production db, creating a new record when necessary and updating data when the record is already available. Allow this duplicates data it also provides some security as ad sales ppl are generally an untrustworthy lot and prone to change infomation as it suits them (a cynical view I know). I created a relationship between the two dbs and then used several setfield commands to copy the data. Comments?? If there's a better way, I'll gladly do it phil courterelle calgary

Is it possible to define a FM script in one table/database that looks in a second table/database and then updates information in the second database based on the search results? here's the background: working on an ad ordering & produciton status project. Ads can be ordered for up to 26 issues using a single form. The order form will be static, that is the information will not change unless the contract conditions change. The production criteria (ad size, placement, proofing, material requirements) for each of the 26 ads can change for each issue. In other words, a client can contract for 26 issues and submit a different ad for each issue. A simple relationship between ad order and production status won't work since the production info will be constantly changing and there will be no way to back track changes from back issues. The match field between the ad order and production dbs is the ad number concatenated to the Issue number: for example NH04-12345 -10, where the last two digits are the issue number. The bottom half of the AdOrder db will be the interface for ad sales to input the production info. I could separate these functions but from a work perspective this is an effecient way to handle the ordering. Here's what Iwant the script todo: From AdOrder db, search the Production db for adnum + current issue, If search is zero, than create new record based on adnumber and current issue and then pass info from AdOrder db to Production db. If serach is not zero, update the record matching the adnumber and current issue with the new information. Thoughts?/ Your feedback will be greatly appreciated. philcourterelle

Anatoli...I hear you...but if the corporate IT folks set the standard to using Apache as the web server, Lasso 6 is not much use. merry christmas to all.... phil courterelle calgary herald

Like others above, I've done a tremendous amount of FMP development and have been working on web development for about two years, off and on. HTML and CDML have been fine so far but where to go from here? If XML has development limitations as described above, what about PHP? I'm intrigued by PHP's ability to interact with dbs (which is really what it's for I gather). How do XML an PHP compare? Are there advantages of using one over the other? Where do Perl or Javascript fit in to this equation, or do they? Finally, I find it somewhat disconcerting that a web site devoted to FMP would move to mySQL..obviously they've outgrown FMP and required better performance...but there is an irony here... sorry to have more questions than answers... cheers Phil Courterelle Production Coordinator Calgary Herald

sktajiri ..thanks for the response...good to know that OSX comes packaged with Apache/PHP...just another reason to upgrade...so if I understand you, PHP replaces CDML entirely? When using PHP to access the FMP db, do you stil need to activate web companion? Garry, thanks for the links...I'd actually read both before and they piqued my interest in using PHP...what's the relationship between XML and PHP? cheers

In recent weeks there has been some discussion about using PHP with FMP. It also appears that some have had success in linking FMP dbs with PHP. I am hoping to spur additional discussion on this topic with this post (primarily for my own edification). As I understand, PHP is a scripting language developed to link high-end databases to the web. PHP scripts are embedded in HTML documents and provide the link betwen the web server and the db. This adds security to the db since it is not published on the web per se. PHP is closely associated with SQL dbs and Apache web server software. In my situation, the IT boys are currently not interested in publishing FMP dbs directly to the web but have offered to use an SQL ODBC infrastructure as an intermediary (IT boys like make work projects is my guess). If I understand how PHP works, PHP scripts are included in the html and when processed by the web server, query or post the information to the db and then upload the page to the user's browser. 1. Does PHP access FMP through WC? (I assume it does) 2. How robust is this process? 3. We're using Apache on Linux. If I'm using a Mac, how do I test the PHP and dbs without access to the web server? Thanks for your input. phil courterelle Calgary Herald pcourterelle@theherald.southam.ca

Bevin, Garry...thanks yet again...I was attempting to use a single search form to access info from two dbs using tokens but this has not worked because, as you've both noted above, when the -find request is processed the replacement tag has not been set so the token remains empty. One previous suggestion was to use javascript (which I've only begun learning) so I'll have to see... you advice and counsel has been welcome and helpful.. cheers phil

Bevin, Garry..thanks for the responses...the mist appears to be clearing... I'm calling for the reply page with <input type="Hidden" name="-format" value="somepage.html"> As I understand it, this page cannot be called unless the request goes through WC. How else could the the data retrieved? Garry, I think some of my confusion is between forms and links in regards to WC....is there a difference between a CDML request processed through a <form> as compared to a link <a href=fmpro?...></a>...? Both are processed by WC, or are they? Should the latter be used for -find, -edit and -new request and the former simply used to display information on the -format page? My continued thanks... phil

Bevin...thanks for the input..If I understand you, your saying the CDML reference is incorrect...According to the CDML reference, "Whatever value you set -Token equal to can be retrieved in the format file by using [fmp-currenttoken]..." If what you're saying is true than it should apply to the -new request but not a -find request. In the latter case, the record already exists...so why would the token still not work? pc

Garry...then we're back to square one, no? if the form is: <form> <form action="FMPro" method="Post"> <input type="hidden" name="-db" value="Nicks.fp5"> <input type="hidden" name="-lay" value="nicks"> <input type="hidden" name="-format" value="results.html"> <input type="hidden" name="-token" value="[FMP-field:Nickname]"> <input type="text" name ="Nickname" value = ""> <input type="submit" name="-new" value="Enter"> </form> why would the result on the returned -format resutls.html be literally: [FMP-field:Nickname] instead of the contents of the field? cheers pc

Vaughan, there are two things being submitted...a -find to scheduling db which is part of the form and a -new to a calendar db which is contained in a url. I agree WC thinks the URL is the path to the format file and I don't think there is a way around it... an html value list is part of a -find request to a db called Schedule. The user can enter a project name, print date or insertion date. I'm using a html value list to direct the results to specific html pages for various departments: Prepress, Pressroom, Distribution, Advertising ect. to help tailor the information the user recieves. When the -find request is process through the Schedule db, everything works fine. What I've added is a url to the Pressroom option in the value list in an attempt to route the -find request to a second db called Calendar. The url is as below...<option value = " "/fmpro?-db=calendar.fp3&-lay=pressroom&-format=pr results.html&-new"> if I place the same url -new request inside a link (<a href=fmpro?-db=calendar.fp3&-lay=pressroom&-format=pr results.html&-new>, it works fine but I am unable to pass the information contained in the print date field to the calendar db for processing...a damned if I do, damned if I don't kind of thing... cheers p <select name = "-format" > <option value = "single_record.html">Full Record</option> <option value = "multiple_record.html">Master Summary</option> <option value = "prepress.html">Prepress</option> <option value = " "/fmpro?-db=calendar.fp3&-lay=pressroom&-format=pr results.html&-token=print date&-new"> Pressroom</option> <option value = "distribution.html">Distribution</option> </select> <input type = "submit" name = "-find" value = "Enter">

Garry...if the line <input type="hidden" name="-token" value="[fmp-field:nickname]"> is part of the form, should it not be passed through web-companion when the -find or -new request is processed and therefore appear on the -format html page as the contents of the field called nickname? I guess the simple question is: when is a request passed through WC and when is it not? Does the placement of the tag in the form make a difference (ie , -db first, -lay second, -format third, -token fourth ect)? cheers phil