11 Replies Latest reply on Nov 29, 2016 2:28 PM by philmodjunk

    Refresh Lookup Values


      So i have 3 look up fields on my one layout. Bottle , Can, Tap. They all display margin pricing. These values are look ups from another table called brew settings and the active layout we are working with is called My List. The look up fields are on the my list table. Here is my issue...


      If i go to brew settings and change the margin price of an item, the change doesn't reflect the lookup fields on the table my list were those look up fields reside unless i go to that item and reselect it to refresh . Is there a way in my script i can have it refresh all the look up fields ? Thanks for your help.

        • 1. Re: Refresh Lookup Values

          There is a Relookup script step, but this sounds like a case where you should not be using a lookup in the first place.

          • 2. Re: Refresh Lookup Values

            Phil you are right, the more i think about this, the lookup field is a stupid idea lol.

            • 3. Re: Refresh Lookup Values

              So after rethinking this, I deleted my lookup fields and just going to use the fields from brew settings. However here is another challenge. I have a field on my layout called typebeerselect. You select if its a bottle , can, or tap. Then it copies the price from the field and imports it another . Here is my script that is triggered after you exit the drop down menu. The issue is tho, you have to reselect the type of beer in the list for the price to now update. Whats a way to fix this or a better solution ?



              • 4. Re: Refresh Lookup Values

                You have provided very little info from which to answer. Why do you need a price field in the active_Brews_List table in the first place? This looks like the very lookup field that I said "this looks like a case where you shouldn't have a look up field".


                A general method for selling products to customers looks like this:




                You set the price for each item in Products that are copied over (looked up) into line items at the time the item is sold to a customer. This is done by creating an invoice record followed by one or more line item records where you select an item and a qty before both a unit price is looked up and used to compute a line item cost. The prices in lineitems would, under ordinary circumstances, never be changed as they are a "snapshot" record of what prices were in effect at the time of sale. Changes can then be made to products as needed, but these changes only affect any future sales--you don't want to go back and change the pricing in previous invoices as that would create invoices with incorrect data in them.


                To get a value list (or other product selection widget) that lists only some products (such as all active brews), you need only add a field to products (brews) that designates which are or are not active.

                • 5. Re: Refresh Lookup Values



                  I understand what you are saying, but think of this as a restaurant menu rather then an invoice perspective. Yesterday maybe that steak was 20.99 but now i got a new vendor and costs went up. So now its 24.99. So i have to change it to 24.99. However there is a different sizes of steaks 16 oz, 18 oz and 20 oz.  So thats where the drop down menu comes into play.


                  So for example.


                  I have a table with  my prices.


                  For a steak 16 oz , 18,oz, and 20oz.


                  I set all my prices.


                  Now i go to my menu  layout and i see 16 oz steak is on there , but the old price is still there. I have to go to the drop down menu and select 16 oz and it copies and pastes the new price of the steak .


                  now the easy way of doing this is to just copy the 3 fields from the other table and display it on this table since they are related, but im trying not to have 3 fields of prices. Hope this analogy some what made sense.

                  • 6. Re: Refresh Lookup Values

                    What is i made the price field a calculation ? Now im thinking how would i write that calc.

                    • 7. Re: Refresh Lookup Values

                      Please read my last post again. Nothing that you have posted changes my advice. It only changes the names of the entities.


                      Instead of invoice, you have a restaurant receipt. The line items are the menu items that the customer requested. Instead of Products you have your restaurant menu.


                      Any business has to periodically change their prices. This should be a single change to a single record for the item whose price has changed. It should not require updating the prices in a second table. Every time you need a price, it should reference that price in that one record.


                      I'm basically questioning why you have a separate table (as it appears to me that you have) for listing "active brews". It appears to me that this table unnecessarily complicates your design--and not just in terms of this price look up issue.

                      1 of 1 people found this helpful
                      • 8. Re: Refresh Lookup Values

                        I scraped the idea after thinking this through.


                        Id have to show you the design of this program to give you a better understanding, but my reasoning was to show someone who is designing an active list at the time what they could make if a person completed all the items on that list. However, those items will change considering seasonal brews etc. So it doesn't matter what a pub could make , its more of what the person paid at the time of drinking it.  I was trying to do it both ways, but at the end of the day it only matters what they paid for it at that time of completion.

                        • 9. Re: Refresh Lookup Values

                          There's no reason why you can't do that, just link to the prices in the Products(menu) table to show the current prices.

                          • 10. Re: Refresh Lookup Values

                            You are dealing with TWO problems and three tables.


                            1) Your real time data entry table for your prices.


                            2) A real time price list table from which to display your current price. This can be a calculated field that uses the changeable records in your price table. You can create various relationships and TOs to solve your problems.


                            3) The invoice table to grab the current price for your billing from Number 2. This will NOT be a changeable calculate field but set by a script using set field and the value taken from the first list above. That list can change but this result must not. Do not use a lookup or calculation as these can be altered by mistake and will change your invoice. Something I discovered 30 years ago when I found that my balances due were changing.

                            • 11. Re: Refresh Lookup Values

                              I agree with JackRodges except for one quibble on 2). you don't need a calculation field for this if you have your relationships set up correctly, you can just link to the price field from the other table.


                              ACTIVE_BREWS::_fkProductID = abs_PRODUCTS::__pkProductID


                              Would allow you to list Active Brews records on a table view, list view or in a portal and include the current price field from Products in that same row of fields.


                              This is, admittedly, a simplified approach to managing pricing and margins on a list of goods or services sold by a given business. Often, yet another table is needed for pricing where you create a new record each time you institute a price change that links to both your Invoicing (or receipt) table and your products (or menu) table. This table includes an effective date field for when a price change goes into effect and allows the system to look up the current price for a given product while being able to schedule a price change that won't take place immediately as well as providing an historical record of each price change--which can be useful info to have when analyzing current and past business performance data.