11 Replies Latest reply on Jul 26, 2017 3:15 PM by disabled_brianb

    Simple 'Price Look Up'


      I'm looking for a means to do a simple price look up for items. I have two tables, an 'inventory' and a 'dispatch'. I'd like to see a price looked up (from inventory) and populated into a field (in dispatch), but only for current records, (not past).  ie. Whenever a certain item number is mentioned in a field, it's price is pre-filled accordingly. I Understand I need to relate the two tables and possible run some conditional scripts, but am unsure how to proceed.



        • 1. Re: Simple 'Price Look Up'
          I Understand I need to relate the two tables

          Assuming you dont have a relationship., can you screenshot the relationship graph as a starting point?


          You may be able to get away with value lists  where you create a list  and reference these settings.



          1) Create value list





          2) in layout mode for Dispatch table  adjust  field settings to reference that value list


          • 2. Re: Simple 'Price Look Up'

            I don't know about simple, you can do that using a lookup or calculation, but here's a sample I made a few years ago to pick thru:

            • 4. Re: Simple 'Price Look Up'

              I'm kinda new to relating tables, portals and all the possibilities there, this is all very useful. thank you.

              • 5. Re: Simple 'Price Look Up'

                sorry i was just updating my first comment.   My value list only applies if the   inventory:price fields are static for all customers

                • 6. Re: Simple 'Price Look Up'

                  Not sure what your needs are, but the data model and relationship seems a bit atypical.


                  I think a  restructuring will allow more possibilities and better control over what appears to be an "invoice"


                  an example




                  1 customer can have multiple orders;  1 order can have multiple items


                  the Customer table contains only the customer information

                  Order contains only the Customer ID, Item IDs, and Summary calculations

                  Items contains all relevant item information such as price



                  I will create a generic one real fast and attach an example

                  • 7. Re: Simple 'Price Look Up'

                    Thank you all,


                    Side Note: I wouldn't be surprised if my database is ENTIRELY atypical. I'm self taught, and running a very busy business which is currently using the database and it has evolved into something I barely recognize. Dispatch records, Inventory, chat live with techs in the field via iPad and using it as a  POS at the front of our brick and mortar shop. I'm surprised I managed to get this far.

                    • 8. Re: Simple 'Price Look Up'

                      The relationship that you show in reply #3 isn't going to work. In order to link a record in Main Base to a record in Key Crew Inventory, you would need the same exact value in all 7 amount fields.


                      You need a separate record for each item just as brianb has recommended.

                      • 9. Re: Simple 'Price Look Up'

                        sorry for the delay I had to pick up the kids.  I was going to create a sample solution, but noticed they have a starter solution already made.   I recommend taking a look at it and see if this something that is close to your workflow and process.

                        • 10. Re: Simple 'Price Look Up'

                          Thanks for all your help fellas. As I work through this, I'll let you know what I did.

                          • 11. Re: Simple 'Price Look Up'

                            yeah good luck.  the main idea is to keep the data separate  then use ONE common data point as a way to connect the tables.  in the example below 



                            -the customer table has a single record for each customer and only contains customer related informatio

                            -The customer is assigned an ID

                            -That CustomerID is then added to an invoice table when a new invoice is made, The invoice only identifies the customer by that ID and then references that ID in the Customer Table to pull all related info


                            This allows one customer to have many invoices/orders   or a 1-to-many relationship  designated by the ---<

                            you can then pull up how many times customer "1" has ordered, paid, frequency, etc..


                            the same idea applies to the  invoice---<order  &  order>-----Items;    1 invoice has many order entries & 1 item can be ordered many times





                            Customer---<Order/invoice----<Order Details > -------Items   


                            Table1 Customer

                            CustomerID   Last name      First Name  Contact  etc.....




                            table2 Invoice

                              InvoiceID     customerID    OrderDate  Total   Payment    #of items etc....

                                   A                   1

                                   B                   1


                            table3 Order details

                                 OrderID    InvoiceID  Item ID   QTY  Discount etc....

                                      a1            A             213

                                      a2            B             522


                            Table4 Items

                               ItemID     Manufacturer  Vendor   Cost      Price etc....

                                 213               abc            xyz        5:00    10:00