Jump to content


  • Posts

  • Joined

  • Last visited

brwnis's Achievements


Apprentice (3/14)

  • First Post
  • Collaborator
  • Conversation Starter
  • Week One Done
  • One Month Later

Recent Badges



  1. I think I must have missed the boat on this one. I've attached my file; my value lists seem to be acting very funny on several fronts: not only are they not updating when new records are created in the related tables (I tried creating the button you suggest, but wasn't able to find a way to define it with the exact command above), but even existing records are only showing up when certain conditional values are present - ie, if companies A,B and C already exist and all have contacts associated with them already, only the contacts for company B are showing up. The same problem (with the same construction) is present in the portal farther down, where I am identifying Suppliers through a value list, and based on that selection, getting a conditional value list of Products from that Supplier. But it's only working for a couple of the suppliers! Being new at all this I'm sure I must be missing something obvious - can someone take a look? I really appreciate the help!! ContractsWorkingCopy8.zip
  2. Comment, Thanks for looking into this! Do you mean a script for the button? I haven't gotten that far yet; I was just trying to check and make sure the relationships worked so far, by just switching between layouts manually, but ran into this hiccup. Would the steps you mention be included in the button script then? Thanks again!
  3. OK so I have a Contracts table related to a Company table by a CompanyID and the Company table is then related to a Contacts table by the CompanyID. I have a layout based on the Contracts table where I can enter a CompanyID from a value list and then have only the Contacts associated with that Company show up on the ContactID value list. However, it only seems to be working with values already existing in the list. I want to be able to include a button on the layout so that if the user finds the Company (or Contact) they are looking for isn't already included, they can click the button and go to a layout to add the contact, then return to the Contract layout and the new Company/Contact will be included on those lists. It works for Company - go back and the new Company pops up with the others, but not for Contacts at that Company. Even if I try to only add a new Contact at an existing Company they still don't show up. I tried redefining my value list to show all (versus only relational) records, then went back and selected my new Contact, and then reset the relational records value list definition, and it then recognized my new contact. But time and time again this is the only way I can get it to work, which obviously is wrong. Any ideas what's going on?
  4. T-Square, Thanks again for all your help - you have been a lifesaver in this process! I will take your advice and move on (for now!) to the Value Lists forum... thanks again!! ~r
  5. OK, so scratch my last question - I was able to just select "use only related records" in the Define Value Lists, and it seemed to be working, BUT, it only seems to acknowledge already-created contacts. When I select a PurchaserID in the Contracts table layout, I now get all the ContactIDs only related to that PurchaserID, which is what I want, but if I go and add another Contact to that Purchaser on the Contact table, it doesn't get added to the Value List unless I go back into Define Value Lists, select "use all records", then go back to Contracts, and select the new ContactID. Then, if I go back and re-select the "use only related records" in Define Value Lists, it will acknowledge the new addition on the Contracts table, but only if I go through those steps. I feel as though I'm missing something fairly basic, but I can't for the life of me figure out what I'm doing wrong. Does anyone have an idea? Thanks again for all the help - this truly is a wonderful resource!!
  6. I already have a table called Companies which relates Contracts to a TO of Contacts called ContactsOnCompany linking the PurchaserID in Contracts to the CompanyID in Contacts. Do you mean that I need to create another set of relationships like this one? Thanks again! Rachel
  7. Lurking unlinked lineitems lurking unlinked lineitems lurking unlinked lineitems... three is about all I can manage! David, all kidding aside, thank you so much. This has gotten me over the hump, so to speak, and cleared up many things I was not quite sure of. I've got many parts of this now working the way I want them, and have mostly formatting left to do! One more question before I get out of your hair, and please let me know if the correct protocol would be to move this to another forum or just wrap this up here with you: I can't seem to get the Contacts drop-down menus to be based on the Purchaser ID. Ideally I'd like to insert the Purchaser ID and have only those Contracts associated with that particular Purchaser show up on the drop-down. How do I manage something like that? Again, I can't tell you how much help this has been to me. I really appreciate it - you have opened up this product for me. Thank you!!
  8. T-Square, You have no idea how much your help is appreciated! However, I'm afraid I got lost in the middle of your instruction. I created the portal using the linked LineItems table as you instructed, but I need more than just a Price linked to each item. I had constructed the LineItems table as I thought you would given that I need all those items, but I'm afraid I must have done it wrong. I have posted the db and perhaps you could take a look and tell me where I am going wrong? You'll see that I've included two portals in Contracts, actually, as I need to show Product information from LineItems in one portion of the Contract, and then Invoice Dates and Amounts from the same LineItems table in another portion of the invoice. Hopefully it will make sense. Again, I can't tell you how much I appreciate your help with this - I am the only person I know using this product and I love it, but have no one else to ask! Thanks again! ContractsDemo.zip
  9. T-Square, I now have the tables and joins set up as you suggest. I keep stumbling over what I am sure is a very basic point about how to set up a Join table, and I am sure I'm probably just over-thinking, BUT: I have one record per contract, and one record per product, but each contract may have many products. So, in my LineItems (Join) table, I should be able to have a field for the Contract ID, and then do I need to include lots of Product ID fields? Seems like there should be a better way than to just duplicate the field 20 or 30 times, but if I only include one Product ID field, won't I only be limited to one Product per Contract? This might be the same question put another way: I'm assuming my LineItems table will house all the information I eventually want on my contract about the products as well, and the per-product calculations (I need to calculate the Total Fee, included on the Products table divided by the number of Supplier Locations from the Supplier Table, which is related to the Products table) that I need will be done in this table. So, in LineItems do I need to include say 20 Product ID fields, 20 Price fields, 20 Supplier Locations fields, 20 calculation fields, etc? Again, I realize this is probably very basic for most - I truly am grateful for the help.
  10. T-Square - you rock!! I knew my original post had laid it on thick and had just about given up on getting any kind of answer..! I really really appreciate your help. I'm going to try this tonight and see what I can put together. I'll let you know if I come up with questions (and will do my best to keep them brief!) Thanks again!!!
  11. After several attempts I have gotten very close, but have been unable to get all my relationships to work correctly. Here is what I am attempting to do: Contract Database: -Each Contract has a unique ID code already assigned -Each Contract has three sections: Purchaser (Contact), Products, Invoice Schedule -Each Contract is related to 1 Company and 1 Contact -Each Company may have more than one Contact -Each Contract may have many line items of Products -Each Product line item includes date fields, number fields and text fields -Each Product is associated with a Supplier -Each Supplier is associated with more than one Product -Each Product line item must calculate a field based on a manually-entered Price (which also is included on the line item with the Product info) -Each Product line date corresponds to and Invoice Date at the bottom of the Contract -Each manually-entered Price is used to calculate an Invoice Price *I would like to be able to have a data-entry layout where I can enter the Contract ID code, the Contact info,enter the IDs for the Products, the Prices for the Products - THEN have FM (a)determine if this is a new or existing Contact(: create a new Contact if it is in fact new, © pull up all the related info for each Product (d) Calculate the amounts in each Product line based on the Price (e) insert an Invoice Date based on the Product date and (f) calculate the Invoice Amount based on the Price. I have a Data Entry table related to Company Table related to a separate Contacts Table. The Data Entry Table is also related to a Products Table which is related to a Suppliers Table. However, if I have many Products on one Contract, all of which need to pull info from the Suppliers Table and do separate calcs, is this what a join table would be used for? I've read a lot about joins and portals, and was thinking that including a portal on my actual Contract Layout (when I get there) that showed joined records from a Join table would be the way to do that - does that makes sense? If so - some advice on Join tables (or examples) would be really helpful - everything I've seen so far has had a join table that shows just the corresponding IDs and maybe one extra field - am I missing something? I was hoping to lay-out the Product line items in the Join tables, so I could just have the portal point there, but that would put a lot of fields in the join table, so I have a sense I must be missing something conceptually. I actually really enjoy this stuff and am having a great time learning, but I've spent so much time building and re-building I'm afraid my head is spinning a bit... can anyone help me? I really appreciate it!
  12. I wish I could help, but am only here going to back-up your request - I have the same problem. I have contracts I am trying to create and originally had attempted repeating fields for the line-items on the forms, but was advised against that. I am now attempting to put together a join-table between my contract data table and my product table to include all the line-item information for the contract, and then will create a separate ContractPrint layout. I was thinking about including a portal into the join table from the print layout, but if portals can't be slid out of the way for printing, then I'm not sure how to proceed... someone with more experience than us - please help! Thanks!
  13. Almost. But actually each flight could belong to many contracts, and each contract could have many flights. This is where I get hung up. I was considering using a join table but I've never done that before - does anyone have any advice? Thanks again!! --- Rach
  14. OK that's the same answer the last guy I talked to gave me, however, I'm not sure how to do a "relational structure" (Bear with me here - I do realize this is the whole basis for FM; just having a conceptual problem getting around this I think). You (and he)didn't give much extra advice so I'm assuming this must be pretty simple, so please forgive me if this is a remedial question: Each contract could have as many as 13 or 14 or more (or as few as one) flight, and I want to be able to input the Flight identifier, and have it pull up the related dates, locations, etc. for that flight. Also in each line (flight) item I need to be able to input a fee, and have it calculate a per-location fee. I then need the invoicing portion of the contract to be able to base the invoice dates on the on-air dates from each flight, and invoice amounts on the calculated fees. Do I just load up the contract layout with 13 or 14 "Flight" fields, "Start Date" fields, "End Date" fields and "Fee" fields? And then 13 or 14 "Invoice Date" fields that look at each of the "Start Date" fields and "Invoice Amt" fields that look at each of the "Fee" fields? If I do all that will I be able to use the slide function or something so I don't have a ton of blank space for 1 or 2 flight contracts? I briefly tried that but couldn't get all the information to pull up correctly. However, if I knew I was barking up the right tree I could spend more time and figure it out. As far as the lookups go, it was the only way I could see to get a snapshot of the information as it was pulled from other tables at the time the contract was made. Is the auto-enter function you mention a script or something? Again, please forgive the ignorance; I'm teaching this to myself and am under an unfortunately tight deadline! Not the best design environment. Thanks again for your help!!
  15. I'm putting together a db to create contracts in selling air-time (broadcasting). Each contract includes the air-time purchase (called "Flight"), the fee for the purchase, and calculations having to do with the breakdown of where the material is to be broadcast. I need a way to be able to list an identifier for each flight, and have relevant info (air-dates, number of broadcast sites, etc) about that flight be pulled from a related table. I also need to be able to reference the air-dates that are pulled to include in an invoicing portion of the contract. I have been able to accomplish this to a certain extent using repeating fields, but was running into problems inserting calculations into the fields that were repeating (fee per broadcast site). The first field would run the calc but each field following wouldn't pull all the info needed for the calc. I was recently advised that I might "insert" a related table instead of using repeating fields, but I have no idea what that means. I have been using Lookups in my repeating fields as I need to preserve the information as its own contract. Am I trying to do too much?..Help..?
  • Create New...

Important Information

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