esegal

I basically have that. The concept I have going was to enter all info in the "Master" Table, then sum all COs data in the "Change Order" Table via calculation, then via relationship (company=company and/or serial I may have my strategy wrong, but it makes sense to me and to a large degree I'm committed to using it. I'm hung up in that it's just not working. I need a fresh set of ideas to clear the cobwebs in my head.

Thanks for the response. I think your first link may be useful. To clarify the process: Each CO needs to auto-calculate based on previously *approved* CO's per contractor, so I've been setting up the file to select values from records that have been approved previously. Because I need a trackable history, no changes to data can be retroactive - it can't update or change previously approved COs. Each time a CO is entered (per contractor), it's amounts should not update in the record's summary until the record is marked "approved", since it can be revised or voided. Once approved, the summaries in that record must update, but not the previous records, hence the [serial It's a bit difficult to explain - I don't speak the FM language very well. So far as being diligent about the bookkeeping, I am trying to set this up for others to use, so it needs to be completely automated and "fool proof".

I need some help here. I'm a novice at FM and have hit a long-standing snag trying to develop a complex of relationships / calculations. I have a DB that records construction Change Orders "CO" (work in addition to contract work). Each change order has a summary section that records the following: 1) Original Contract Amount (easy) 2) Sum of Previously Approved COs 3) Contract Sum Including Previously Approved COs 4) #3 Plus the current CO amount First I have a Master_Table into which my data is entered, then I have a CO_Table that summarizes all CO information entered into the Master_Table. In the Master_Table I have fields that retrieve data (listed above) from the CO_Table via relationships. There's a [Company=Company] relationship and another for previous data [Company=Company] & [CO_Serial So far it's not working well, or one field starts to work then another will stop. Can someone on the forum please draft up a schematic that I can follow to straighten this out? I confess I'm a little slow on picking up the relationship aspects of FM. I know portals may be a solution, but to date I've not been successful at setting them up for the purposes above.

Thanks. I've been trying to implement this in several ways, but my merge field containing the value list still shows the value list items in the order they were selected, not the numerical order of the list or the order I place them in the FilterValues.

I have an INPUT layout with a checkbox value list. The 9 values begin numerically ( "1)", "2)",...). In a PRINT layout I have a merge field of the same field/value list as a means to list only items that were checked. The problem is that in the PRINT layout the value list items appear in the order in which they were selected, not in the numerical order in which they were named. Is there any way to correct this?

I am on the trial version of FM8 and have transferred by DBs without problems. Yesterday I was host while another was client. We closed out at the end of the day. This morning I get the following error (no error number) when opening the database. I do not get error when related files open. 906 total error(s) occurred during this operation. 906 record(s) could not be modified because they were in use by other users or your priviledges do not allow you to change them. I still have 906 records according to my auto-serial, but there is no visible data in any record other than any global data. I cannot tell if the remaining data still exists or not, but am hopeful because the file size is similar to before the problem. Anyone familiar with this error and know how to fix it? [edit] FYI - all files are set for administrator priviledges, so that should not be a problem. Also, no errors were reported when closing down at the end of the day. I know a server should host the files but this day it was not possible. [end edit]

I'm just not getting results and I'm trying my best. Questions: I have my MASTER table and my CHANGE ORDER table. Every record has unique auto serials. The MASTER table is where the bulk of my data is currently entered/stored. The fields in my newly created CHANGE ORDER table populate from the MASTER via LOOKUP. I cannot figure out which table to enter info and which table to calculate it. I need a fresh set of eyes because I'm mired down with the way I've been trying to do this and am totally frustrated with myself.

to answer your other question, the serial is set with Get From Previous Record + 1. This is in order to be able to revise a CO by adding & "R" through a revise script, so they are not auto-enter serials, per se.

Ah - I missed your "<" sign. Well, it still doesn't work for me. Here's what I need to calculate: 1) Original contract amount (from "A" below) 2) Sum of all prior COs (change orders) (trying to use "D" below.) 3) Contract sum including prior COs (calculation) 4) New contract sum including all COs (calculation) I have #1 & #4 down, but can't get #2 & 3 until I resolve #2. Relationships between tables: A) proj_company = proj_company co_no = co_no C) proj_company = proj_company AND co_no = co_no D) proj_company = proj_company AND co_no < co_no I know there are too many relationships, I will get rid of those I don't need. I've just set them up to accomplish things along the way. If I try to combine them they don't work the same so I keep them separate. Now it's a mess. I'm not good at relationships yet.

What you describe is what I set up, the problem is that the sum I get is for all records related to the company either before or after the current record, not just the records entered prior. There can be more than one record per day per company so date won't work. I don't know if a date & time-of-day reference would be the right way to go, or how to do that if it is the right way to go. What might work is to sum only records related to said company with a serial number "less than" the current serial number. I just don't know how to do that yet.

Sorry if this is a really newbie question - I have a calculation I am working on that requires me to calculate the sum of a field entry for all previous records for a particular company excluding the same field in the current record. How do I get a sum for previous records only? Also, I already have many records in place and I need to implement this from the first record, so I have have to summarize previous records by date since they were created chronologically. Thanks.

That's what I was trying to accomplish. Each layout gets it's own record_type field (as well as the Contact_ID and Company relationships) so this should work well. Each record also gets a serial no matter what record_type it is. I am not savvy enough to nail these things down just yet. Thanks to those who are.

I'll work on developing the solution as you both illustrate. I know I need to get my relationship knowledge together. It's a concept I've been struggling with but am getting better as I spend more time developing them. Unfortunately FM8 is not an option for me at the moment, but it sure sounds like they've been listening to their users. Thanks.

Check out this thread. http://www.fmforums.com/forum/showtopic.php?tid/169939/post/176193/hl// I modified the Nightwing script to fit my db and it works, however I am having a problem in that I only need the serial in one record_type, so if I reference the same company in another record_type, it advances one serial number even though I don't want it to, so when I go back to the record_type that uses the serial number it appears to "skip" one. I am trying to find a solution to that. If you only have one record type in your db this will work okay.

Thanks so far, but one more question - how can I modify the calculation so the next serial number is calculated and input ONLY for one particular record_type?

I have a PROJECTS db that contains layouts for every type of document I use during a project, particularly contracts & change orders. Now, each contractor in a project has one CONTRACT record in the CONTRACT layout, and then will have multiple CHANGE ORDER records afterwards in the CHANGE ORDER layout. So, on my home screen when I select NEW CHANGE ORDER I am taken to the CHANGE ORDER layout and a CONSTRAIN FOUND SET script step isolates all records with rec_type CHANGE ORDER. Now, when I select a contractor in a new CHANGE ORDER record I would like the contractor's CONTRACT data (from another record that is omitted via the constrain script step) to be auto-entered in some fields (contract date, contract amount, etc.). So how do I call up specific data from a field in a record that has been omitted? I think another table would do it, but so far I have not been successful as I am still learning relationships and scripting. Thanks for any help.

A it turns out the 002 is due to my reusing my previous field that had a "+1" condition on it. I fixed the field and the error using your fix, so it's fine now. I'm going to use it for a while prior to releasing it to my office. Thanks for the assistance! This is going to be very useful tracking information across the 60+ companies on this project.

I've integrated the Nighwing script and it works...and then again it doesn't. For some reason the first record came up as -002 instead of -001. As I tried to update my previous records (17 so far) they were all off by one, making record 17 is -018. When I tried to correct -002 it became -019 and so on. I can't seem to start over. If anyone is familiar with the Nightwing script, is there any way to override the numbers?

I've searched and have not found a solution for this. I am a novice/intermediate user so bear with me if the solution is simple. I also hope this is in the right forum, please tell me if it's not. I have a PROJECT_DB with a layout called "RFI". I have a CONTACTS_DB where each individual and their company also has a representation of three initials (Universal Consulting Engineers = UCE). In the PROJECTS_DB, when I enter a contact into an RFI record, also linked would be the three initials for their company. What I need is the serial number to be contigent on the initials, so the first few records might be: UCE-001, UCE-002, ACM-001, UCE-003, ACM-002, UCE-004.. whereas each company has it's own serial number series that advances one each time an RFI is linked to that company. Oh, and I may have to delete a record before it is implemented, so I'd rather not have an auto-enter serial because if I delete the record without implementing it I end up with a gap in the sequence. Need help soon as the DB is active and I need a solution before I create too many records.

Why not duplicate the layout and set all the field behavior so that none of the fields are accessible. You could also make a duplicate layout of merge fields. When someone makes a NEW record you go to a record for entry. If you go to REVIEW your records you would see the inaccessible layout. Only the administrator would have permissions to access the data entry layouts. The script solution might be easier, but I thought I'd throw out an alternative.

In my PROJECT db I have many records referencing many contacts, but many records keep referencing the same person (for instance in a transmittals "TO:" field). The people are referenced from a CONTACTS DB. I am trying to make a list in the individual PROJECTS db showing each contact's information used in the file, but when I try this it shows a list of the contacts in the order they are in the db records, including duplicate instances. I'm not a master at FM, so forgive my ignorance if the answer is obvious. Thanks!

Hmmm. Your suggestion does work in table view, but still only works in the Trailing Grand Summary.

That's exactly what I was looking for! Thanks for the sample.

Can you be more clear as to the additional summary field and how it ties into the sub-summary? I am adding a summary field as you mentioned "Sum ( ThatCheckboxField )", but the sub-summary only shows the number of signed contracts, not the total of the contracts that are signed. I assume I need to set up some sort of If/Then arrangements, whereas I spell out that "Sum (Contracts_Total) If (Signed = "1")" or something like that. I'm still a novice at scripts, so please bear with me.

I have a layout which really looks and works best in Table View, but it requires a Trailing Grand Summary Part with summary fields. I can only get it to print in the Footer which makes it appear on the first page, too. Is there some way to get the summary on the last page only? On the first page it is confusing because the Sum = more than the parts on the page.