Newbies hawkins6423 Posted May 21, 2009 Newbies Share Posted May 21, 2009 Hello all, I am making a solution in FMP10. It is an invoicing solution. I have a portal in my "Invoice" Table, that has a relationship with "Inventory" via a join table. The portal works great as far as when the employee types the part number in the invoice, the rest of the information is automatically added (price, short desc., etc). We are in the aftermarket automotive industry and are looking to make it so when the part number is entered (first part only (of the portal)) it will automatically insert the year make and model that part fits in a seperate feild. So pretty much in more of a layman's terms.... I have a portal and based off the first value entered (the first part number), I would like it to look up a value from a different table and insert it in a feild by the portal. Thanks!!! Link to comment Share on other sites More sharing options...
tv_kid Posted May 21, 2009 Share Posted May 21, 2009 Hi So you are wanting to choose a product and have the relevant details (price, dimensions etc) filled in automatically? Are you familiar with using a value list? You can set up a value list of products, then attach this to the first field. Once the product is selected, if the other fields are set as lookups based on the product id they will fill in the required info. Note that I am suggesting product id here and not just the product name. You will want to ensure you are using a unique value to avoid confusion. Hope this helps get you started. Link to comment Share on other sites More sharing options...
LaRetta Posted May 21, 2009 Share Posted May 21, 2009 will automatically insert the year make and model that part fits in a seperate feild. Sometimes lookup is necessary (to keep what the product displayed back through time) and sometimes it isn't necessary. Just a few examples of why I bring this up ... 1) Some businesses want to plant the product NAME in a field in the invoice LineItem as well because, if they change the words of the product description, they need to know what it said AT THE TIME of the invoice (it is a liability issue). But usually most businesses don't care if the name itself changes and so planting the name isn't necessary. 2)Usually you will want to plant a customer's address within an invoice (lookup) because the customer may move and you want to know where the item was shipped three years ago. But many times, you can simply place the related field directly on your layout and there is no need to plant redundant data in another table. In each instance, you should get clear with the owners when to plant and when to simply place related. Link to comment Share on other sites More sharing options...
Newbies hawkins6423 Posted May 21, 2009 Author Newbies Share Posted May 21, 2009 I already have a portal that displays the products and the relevant details. I am not familiar with using a value list, but thats probably what I need. So if i got what your saying correct, I can make a value list from the first feild in the portal (part number)? Also yes my part numbers are unique values. Thanks for the tips and help! Link to comment Share on other sites More sharing options...
tv_kid Posted May 21, 2009 Share Posted May 21, 2009 Here's a rough and ready example to give you a starting point. But do take heed of LaRetta's advice and think about what you are trying to achieve. Personally I prefer lookups in this situation, but your requirements may differ. Matt Invoice.fp7.zip Link to comment Share on other sites More sharing options...
Newbies hawkins6423 Posted May 22, 2009 Author Newbies Share Posted May 22, 2009 Matt, I already have what you did in that database. I appreciate your help a lot. I attatched my database.. the one im actually working on. If you go to the "DTA Sales Invoice" and put in a part # (i deleted all records to upload to forum) I would like the corresponding "Vehicle ID" from dta inventory to appear on the invoice via a lookup based on the FIRST part number in the portal. I think youll understand if you open it up and take a look at it. Thanks a lot. Actually the file is too big to upload on the forum so I put it on my server. www.discounttruckaccessories.com/dta.zip (thats the database zipped) Link to comment Share on other sites More sharing options...
tv_kid Posted May 22, 2009 Share Posted May 22, 2009 What are the user name / password for the database? Link to comment Share on other sites More sharing options...
Newbies hawkins6423 Posted May 22, 2009 Author Newbies Share Posted May 22, 2009 (edited) the guest account should work..... if not try Ivan is user name and nothing (actually nothing as in dont type anything) as the password. Edited May 22, 2009 by Guest Link to comment Share on other sites More sharing options...
tv_kid Posted May 22, 2009 Share Posted May 22, 2009 the guest account should work I didn't think to try that! I would like the corresponding "Vehicle ID" from dta inventory to appear on the invoice via a lookup based on the FIRST part number in the portal. It looks as if you have tried to do this already, as the 'VehicleID' field in the Invoice table is set to a lookup, but with no value chosen. You could set this field to a calculation that referenced the VehicleID from the first matching 'Vehicle Make' record (via 'Lines' and 'DTA Inventory'), but this won't work with the way your tables are currently related. 'DTA Inventory' and 'Vehicle Make' are currently linked via the 'VehicleID' field, but the lookup options in this field in the 'DTA Inventory' table don't appear to make sense, as you are asking it to lookup a value based on a relationship established using that value (this probably isn't very clear, but take a look and see...). You're going to need another way of establishing the 'VehicleID' in the 'DTA Inventory' table first. Actually, now I look at it a little more, I think I see where you are going wrong. In the 'DTA Inventory' table you have a drop down list of vehicle makes. I presume your intention is then to use this to cross reference the 'Vehicle Make' table. If you were to reverse this thinking, and set your drop down list to use the 'VehicleID' values from the 'Vehicle Make' table, you would then easily establish your relationship and would not need to maintain the vehicle makes in two places at once (the value list and the 'Vehicle Make' table). You could then easily extract the vehicle make to the invoice, or all of the makes if you sell parts for more than one vehicle on one invoice. Does this help or have I lost you with my garbled explanation? Link to comment Share on other sites More sharing options...
Newbies hawkins6423 Posted May 22, 2009 Author Newbies Share Posted May 22, 2009 Man thanks a lot for looking at it, I keep reading your explination trying to understand it haha. I forwarded your response to a friend of mine that is helping me with the database. Pretty much from what I can understand.... your saying that the way I have the vehicles drawing from a seperate table causes issues when drawing from the referenced feild. Man thats complicated. Thanks for the help let me know if you have any ideas. Link to comment Share on other sites More sharing options...
tv_kid Posted May 22, 2009 Share Posted May 22, 2009 (edited) your saying that the way I have the vehicles drawing from a seperate table causes issues when drawing from the referenced feild. Man thats complicated. Not necessarily, what you are trying to do makes sense, I think you are just going about it in slightly the wrong way. You're not far off though. Sorry if I've confused you with my explanation. I've attached a modified version of your file, with the unnecessary tables removed to reduce the file size. Hmm, seem to have a problem attaching a file, despite it now being below the max size allowed. I'll send you a PM with a link... Edited May 22, 2009 by Guest Cannot upload file Link to comment Share on other sites More sharing options...
comment Posted May 22, 2009 Share Posted May 22, 2009 How about this way? Note that an invoice cannot do a lookup based on a selection made in a line item - but it shouldn't matter, since the related value will update if the selection is changed anyway (once the record is committed). FirstProductAttribute.fp7.zip Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 5445 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now