susan siegel

Thanks again for pointing me in the direction of the "replace fields" command. Now I understand why the script was not working for all the records in the found set. Susan

Thanks to both Enders and Geoff. I think I understand it now. 1) if I use a calculation when I define a field, it forces a result for all records -- BUT 2) if I use a calculation to "replace field contents" , then the calculation only applies for the found set and doesn't change what already exists in the field for all the other "non" found records. While the above has resolved my immediate problem, I'm still left with one unanswered question for future use: does a script work for only one record at a time or can it be applied to an entire found set? Thanks again. susan

I tried a calculation field in the new field 3 using an IF statement. I don't remember the exact language or punctuation, but it went something like this: If (field 1="x"; "1"; "0") This worked fine for bringing the contents of field 1 into the new field 3. But, if I'm remembering my experience correctly, I encounted two problems. 1)the calc formula only worked when entering new records, not for changing existing ones. (I may not have had the field coded correctly for validation; I have to take another look at that.) 2)because field 3 was a calc field, I couldn't modify the data, e.g., changing the "0" of field 2 to a third option if both field 1 and field 2 were empty (which does happen). I tried changing the calc formula to a new IF statement so I could account for field 1 and field 2 being empty but that didn't work; I don't remember why. BUT -- even if I can get the calc field to work correctly -- for future use -- I still need to know what I was doing wrong with the script and why I couldn't get it to work for the entire found set. Susan

I'm trying to copy and paste data from an existing field to another for a specified found set, basically trying to "fix" a database that was not set up correctly in the beginning. I've set up a script that works - but only for the first record of the found set; not the others. I think I must be missing a basic step somewhere but can't figure it out. Here's a simplfied version of what I'm trying to do. Field 1 (an existing field with data, e.g., "x") Field 2 (an existing field with data, e.g., "y") Field 3 (new field that will contain either "x" or "y" or be empty if fields 1 and 2 are empty) One of many different script versions was: set field (field 1) select all copy (field 1) set field (field 3) select all paste (field 3) I would then repeat the script changing field 1 for field 2. I've also tried alternate scripts with "insert text" into the new field instead of the copy and paste approach -- but again it works only for the first record in the found set. Thanks.

Many thanks to all three responders. (I hope this one response suffices for all three.) Your examples have given me several options to test out for different databases and data entry practices. I get the gist of creating a new calculation field to get rid of the extra spaces. It seems that using the "substitute" option is the best because it automatically corrects the original field for me. The other options, including adding a "spacecheck field" alert me to a problem that needs attention. I have different types of databases with different entry patterns and different histories as to how they came into FM8 (some via import from other DBs and some new in FM). In some cases, the extra space or carriage return is inserted at data entry (my carelessness). In that case, the 'substitute" should work fine. In other cases, I might be modifying data, e.g., initially the field might have an "x" indicating an active record. Then, at a later date when the record becomes inactive, the "x" is deleted. I've got some experimenting to do - but you've put me on the right track. THANKS. Susan

For lack of a better term or phrase, I'll call my problem one of "hidden data" When viewing a record, a field may "look" empty but it turns out there is "something" in the field because the record shows up on a "find" when, based on the find criteria I have entered, the record should NOT show up. (example: The "find" criteria calls for every record that has data in "field x" -- but -- some records come up that don't have anything in "field x") When I suspect that the "find" results include a record that doesn't belong, I go back to the record, and even though the field looks empty, I delete all the spaces in the field again. This usually clears out whatever is 'hidden" so that the next time I do a find, the record does not come up. But - this "solution" only works only when one or more records in the find results looks questionable. If I don't suspect a problem, then I don't know that there's error that needs to be corrected -- and I end up using a flawed "find" list of records. Is there some way that I could "see" that there's something in the field even though it looks empty? Is there a preference like setting that might show something that is otherwise hidden? What am I possibly doing wrong to create the problem in the first place? thanks for any help with this rather mysterious issue. Susan

I'm not sure I'm explaining this correctly - but the problem is that the layout stops showing the sub summary sort after I've gone back into the table to make some data changes. The sub summary is based on the "street name" field (and there's a "street name summary" field )and then there are a series of summary fields, let's just call them a, b, c,d, etc. For each street, I get the totals for each of the summary fields. The first time I set up the layout, after much trial and error, I can get the layout that I want (I'll skip over why I can't get it right the first time.) But - when I review the list of streets, I see that some streets are listed twice -- I assume probably because there's some hidden difference in the way the street name was initially entered. So I've gone back into the file and reentered the street name, taking out the extra spaces that I thought was the cause of the problem. But then, when I preview the layout, I seem to have lost the ability to see a sort listing the individiual streets; I just get one street name listed (the one from the very first record) and the totals for all the remaining fields. My found list includes all the streets. I can't get back to the listing of all the streets. What am I doing wrong?

Many Thanks. And Happy Thanksgiving. Susan

I need some help on how to set up the following type FIND in possibly one step. I want to search on the STATE field pulling up only records for certain states, e.g., MD, VA, NC. (I have no problem adding other fields to the find, e.g., those with email addresses in the above states and those who are "active" records.) When I enter the criteria in the initial FIND screen, is there a way I can enter multiple states on the same FIND screen instead of doing individual finds for each state? I suspect the answer may lie in creating a script but I'm new to FM and not well versed in scripts. Or do I have to do a series of repeated "constraints" Or, alternately, I'd like to omit from a search, all records that in the "state" field show SC, FL, GA.

Thanks for your posting. The only reason for my wanting the duplicate field was so that when I changed the original contents using the "replace" command, I could compare the two fields in a side by side comparison to spot any errors. While most of the replacements are a simple single number replacement, some are more complex and involve 2-4 numbers separated by a comma, with each number needing to be replaced. See the last posting from Lee who parsed the field so that I can see ALL the old numbers and the new numbers side by side. I'm not sure anyone else will ever want to duplicate the contents of a single field across all records in the file -- but if they do, hopefully this exchange may help them arrive at a solution that works for them. Susan

Thanks (again), Lee. The parsing lets me see all the "original" numbers so I can visually compare them with the revised ones I'll be substituing and hopefully pick up any errors when I do a mass "replace." I can't seem to open up the script so that I can understand -- and learn -- what you did. So I'd very much appreciate one more posting with the actual script steps. The script may also be helpful in my dealing with the awkward "author" I'm working with where I have multiple author names that have to be separated as well as the names of adapters. Parsing may be the solution to this field also. I hope this solution can be of help to others. Thanks Susan

Thanks, Lee. Looks like you're an early riser. I'm on my way out for a meeting and will look at the file when I return. I fogot to mention (it didn't seem important) that only some of the records have source codes; there's another field I use to sort out only the relevant records. But that shouldn't affect what you did. susan

Hi Lee, The numbers in the "source code" field, #1-164, direct the user to an alphabetical list of 164 specific sources which will be listed, by number, elsewhere in the publication. That's why the contents of this field vary from a single number to 2 or more numbers; some records have more than one source. Unexpectedly, after I listed the source code/s for all 1,600 entries, I discovered that 3 sources had been omitted from the original list of 164 sources. So I have had to renumber the original source list based on a new alpha sort. That means, that all the "source codes" have to be changed. In most, but not all, cases, the new number will be two digits higher than the original number. I want to do this was a mass "find/replace" - but before I do that, I'd like to set up a duplicate "source code" field (call it fields 1a and 1b) so that when I do the mass chanage on "field 1a" I can compare it to the unchanged "field 1b" to see if there were any errors along the way. I don;t want to take the chance that there will be mistakes. I did a test "find/replace" yesterday and think I worked out most of the kinks. I have to change the existing field definiition from its current "text" (which I need because of the commas) to "number" so that when I want to change "12" to "14" I don't also change "121" to "141". And I know from past experience, that when I start with the number replacement, I need to start from the highest number and work backwards. I appreciate the time you're taking to help me find an automatic solution to this issue. In the previous outdated DB software I used, I couldn't duplicate the contents of a field across all records; I assumed that in more up-to-date software, this would be an easy function. Maybe I was wrong and this isn't a feature that many users need. Susan

Thanks again, Lee. Unfortunately, still not working. Attached is a copy of the file. What I am able to do is copy and paste the "source code" field into the new "revised code" field -- BUT - it comes out in one record, as a list. You'll see the "15" in the attached file in the first record. this would be okay -- except for the fact that the leading is different from the original field and after a few records the records (numbers) don't line up properly. In the attached test tile I've moved the two critical fields over the the left so you don't have to bother with the remaining fields. Most of the fields in the source file have just one number and I'm pretty confident that I won't have a problem with "find/replace" on these. My concern is when I have multiple numbers, e.g., 7, 37, 145. The possible numbers range from 1-168 (assigned numbers based on an alpha list from another file. Because I have to add 3 entries to the alpha list, new numbers have to be assigned and transfered over to this file. Susan test810.zip

Thanks, Lee, for the quick response. I hadn't explored scripts yet. I tried your suggested script but still ran into problems. Depending on how I did it, I either got nothing in the second duplicated field or just one entry in the list of 1,600 records. Here's a recap of what I did. I'm obviously missing something. I created a new layout (Layout #9) with just the field I want copied ("source code") and all the appropriate records are there when I view by table. I entered the following for the script. 1) go to layout (layout #9) 2) select all (you didn't indicate this step but it seemed obvious to me as there was no other "copy all records" option.) 3) copy. I had 2 choices here a) select entire contents of field. I checked this. select target field. I tried this with and without checking this box. As "layout #9" only had one field, it didn't seem necessary to check this box. But, when I did check it, I went back to the layout for the entire record and selected the approprite field "source code" 4)go to layout here I tried it 2 ways. a) go to layout (originial layout) as you suggested. But this doesn't seem to make sense if by "original" you mean layout #9 above -- which is the same "source code" field. : so I went to "go to layout(layout with all the fields) and selected "revised code" as the targt field. 5) Paste to (: above. I think this step repeated what was in step 4 above. What am I doing wrong? Thanks for getting me started on scripts. (My first major FM project is getting a database in shape to print out selected fields for a book. Each day there seems to be a new challenge to overcome.) susan

Is there a way I can duplicate (copy & paste ??) the contents of a specific field for all the records in a table? So far, I can't seem to use the 'select all" feature to highlight the field for all records; it just works for a single record. There are no unique fields in the table that I can use for a lookup on a new field. My objective: I have a table with about 1,600 records. One text field has one or more numbers (separated by commas; there's no need to sort the field). Due to a last minute update, all the numbers have to be changed -- usually by making them one digit higher. Before I do a "find and replace" operation across the entire table, I want to duplicate the field and see the old and new fields side by side so that I can eyeball the new field for correctness and/or problems. (Being new to FM, I'm not totaly comfortable with a blind replace operation.) Needless to say, I'm trying to avoid going through 1600 records manually. Any suggestions would be most appreciated. Susan

Thanks. I just started to reply to your posting with more questions -- but as I wrote it out -- the light blub flashed on. By adding an invoice # field to the lineitem table, I can then use the invoice # as a look up field on the invoice table. It's all beginning to fall into place. I won't be able to implement this solution right away, though as I'm working out other FM issues that have to take higher priority. There's a lot of learning going on all at the same time -- and this forum has been invaluable. Thanks to everyone. Susan

You're absolutely right. Two fields is the way to go. The problem is I'm working from a list that I inherited that was in "first name last name" format. To complicate matters, the field includes multiple names (although thankfully most have only one name) with some names in parenthesis (these have to be treated differently.) So once I get the 2 word names and middle initial names done, I still have to edit the list manually for the others -- and pick up any errors. susan

Thanks for the quick response. I'm embarrassed to say that I should have seen that extra dialog box. It's working fine now. Susan

I'm having trouble doing an alpha sort on a last name, first name field that I created as follows: 1. the original field was first name last name (no comma) (field #1) 2. using left and right functions to break down field #1, I've been able to create a new field: last name, first name. (field #2) My problem is that I cannot do an alpha sort on the new field (#2). Is this because it's a calculation field instead of straight text field? Nor can I go back to the "last name" only field for the sort as this is also a calculation field. I've tried to change field #2 into a text field by creating a new field (#3)using the getastext and getas SVG functions but either that's not the solution or I'm doing it incorrectly. I've tried to duplicate field #2 and change the copy to a text field but then the field comes up empty. Any suggestions would be most appreciated. Thanks Susan

Belated thanks for explaining the "one item" per record on the line item table. That was the piece of the puzzle I wasn't quite seeing. If I've got things right now (I'm still doing some tests before putting the finishing touches to the layout), if a customer orders 3 items, I enter 3 separate records in the line item file, one for each item, along with the date and customer number that I use for matching fields. Then -- in the invoice file, I've set up a portal that pulls in the 3 item descriptions, price etc. I add the subtotal, shipping, total, etc and then generate the invoice. I'm having a little trouble generating a report from the line item file that summarizes qty and ext price for each product id when sorted by month -- but I think that with patience I can get this worked out over time. Thanks again. Susan

Thanks, KC. I started off with fresh files following your suggestions but ran into the following problems. 1) You suggested that for the line item file: "The description & price in the line items file are lookup fields, based on info from the inventory file." Sounds okay - but -then how do I get the ability to add several products (rows)? In the product id field, i can add a second and third row with a carriage return -- but when the descp and price fields are seprate look up fields to the inventory file, I don't have that option -- or can't figure out how to find/get it. If I add repeat fields for descrip and price ( I don't think this is the way to go), then they just repeat the first value. Or, is there a way to change that? 2)You also suggested for the line item field, that: "Quantity and discount can be fields in the line items file that you just manually enter the data." Again -- how do I deal with adding rows for multiple products? I only seem to be able to create the ability to add additional rows with a portal. But as this data is first entered in the line itme file - there's no other file to create a portal from. 3) Then, for the invoice file, you suggest: "You need to put the fields from the line items file into the portal on the invoice." that makes sense to me -- but this assumes that I can fix the above problem so that I can have multiple rows for descp, qty, price, etc. If I can do that - then I can put them all in a new portal for the invoice file. So the problem really boils down to the multiple row issue - or so it seems to me. susan

This is an update to an earlier posting with a different subject heading -- but basically the same problem. I'm still going round in circles over how to set up related customer/inventory/lineitem/invoice files. I'm either missing some steps, doing steps out of order, or missing something altogether. My apologies to those who have answered my earlier posting: I'm just not getting something. I've looked through the forum but can't find any examples that explain the procedure in simple enough terms that I can follow. Let me see if I can break it down into simple steps and questions 1. once I get the four files set up and linked appropriately on matching customer id, invoice # and product id, on what file do I begin to enter the actual new order -- invoice or lineitem -- or doesn't it make a difference? So far, I've entered the new sales on the lineitem file, using a portal that brings in description and price for each product from the inventory file. Two problems here: a) for the product list (there are 35 products), I enter the code for the first product and hit enter. (I prefer to enter the code manually rather than scroll through a drop box. But could the absence of a drop box be part of my problem ??) The above works fine -- sort of. to enter the next product, I insert a carriage return and then the next product code and description and price shows up. Fine, except that the full list of product codes doesn't show up -- which creates a problem later on for generating reports. To deal with this, I added a second product code in the portal (this may not be on the file I'm attaching), just before "descripion." Not great looking, but at least it shows, on separate lines, each product code. the main problem then is: where to enter qty, disc and ext price for each product row? There's no visible space to enter this information for multiple rows. The only way I fixed this -- and I know it's not the answer -- was to add qty, disc and ext price fields to the inventory file and then add them to the portal box in lineitem. This creates other problems though in the inventory file, so I don't think it's the answer. Do I need to create a second (or third) portal? If so, where and for what field/s? It seems that creating a portal is the way to deal with multiple rows. But how can I create a portal to bring in qty and disct from another file when information this doesn't exist yet. (I read some discussion about dynamic and static data -- but this didn't help me understand what to do in practical terms.) If - and Once I can get the qty and ext price for each of the products in the lineitem file, then should they be lookups in the invoice file (using matching invoice file #): Or do I need one or more portals in the invoice file?? Another basic question about relationships and lookups that I might be misunderstanding: If two files are joined, e.g., customer and invoice, with matching customer ids, then on the invoice file, do name, address, etc. have to be "lookuped" to the customer file -- or does the relationship automatically pull that information into place on the invoice file? In my test cases, it seemed that I did have to use the lookup for name and address. For reports - I assume that this should be based on data from the lineitem file?? Yes? No? I need the usual type of reports: for a given period of time, qty and total dollar value for each product and then total values for the time period. All I need is the product code; no description. Or a description without the product one; one or the other is sufficient. Once again, thanks in advance for helping a beginner "see the light." As I can only attach two files, I'll attach the lineitem and invoice. They're both in rough form. the customer file is straightfoward and I have no problem using lookup to bring in address, etc. and the inventory file, I assume, should just be product ccode, descirp, price , so there's nothing much to see there. line_item.zip BHP_Invoice.zip

In the past, I've used a <> search to find only a portion of the records in a file, e.g., those with last names >a.. That search is IN ADDITION to other criteria I'm searching for, e.g., only those records that are "active" -- but when, for a specific reason, I don't want to entire list of all active records -- just some. susan

Thanks again. By combining the suggestions in both posts I think I've got it. The key is creating a second field with one word -- a full word and not just a letter. Then I can do >aa... As my "real" file with multiple fields (as opposed to the sample one field file I attached earlier) already has a separate "alpha" file for alphabetizing, I can easily make a slight adjustment to that field and use it as my one word <> search. Thanks for getting me one step along in the learning curve. One of the issues I'm dealing with in learning FM is that I have to learn new ways to perform procedures I've been doing for 15 years in my current DB. susan