8 Replies Latest reply on Jun 24, 2015 7:12 PM by starstuff

    Why is the Product Prices the same as the first Line Item?

    starstuff

      Title

      Why is the Product Prices the same as the first Line Item?

      Post

      Hi All!

       

      i have a problem in the line item product prices, the problem is that the first Line Item gets a price, then after that, the following Line Items will still show the first Line Item price,

      the result im getting is that any products i add even without prices still shows the first Line Item price.

      also, even the products without any price will get a price

       

       

      The Line Items is a portal of PO_LINE_ITEMS

      The price for the line item is a Number field with auto-calculation from table PRICING_SUPPLIER::price_net

      The price_net is a calculation field of base price - discount

       

      The relationship i use is attached.

       

      Thank You!

      pricing.png

        • 1. Re: Why is the Product Prices the same as the first Line Item?
          philmodjunk

          Enter layout mode and try moving the portal. Do all the fields in the portal row move when you move the portal or do these price fields not move when the portal moves it.

          It's possible that these fields are not properly "owned" by the portal object. If this is the case, drag them away from the portal, release the mouse button and drag them back. Do not release the mouse button until the borders of the field objects or fully inside the borders of the portal row. Test by dragging the portal a few pixels.

          Once the fields are "owned" by the portal row, you can use the alignment tools in the inspector to adjust their final position if needed.

          • 2. Re: Why is the Product Prices the same as the first Line Item?
            starstuff

            All the fields are inside the portal, re-created it aswell

            can relationship cause this too?

            im rechecking the fields and relationship again.

             

            Thanks

            • 3. Re: Why is the Product Prices the same as the first Line Item?
              starstuff

              Some results i found while checking,

              i changed the price from PRICING_SUPPLIER::price_net to just PRODUCTS::price

              and inside the portal the price changes

              im thinking the problem is with the PRICING_SUPPLIER and  PO_LINE_ITEMS relationship

               

              update

              inside PO_LINE_ITEMS viewing in Table View, i can see the different fk_product_id, but the price is all the same

              • 4. Re: Why is the Product Prices the same as the first Line Item?
                starstuff

                Hi PhilModJunk!

                 

                i was able to change the prices at last, i added a TO PRICING_SUPPLER_PO_Invoices 2 (the red one on attachment) to the whole relationship (above attachment)

                 

                a new problem arises though, i can only get the price of one of the suppliers, changing the suppliers doesn't change the price at all.

                 

                 

                • 5. Re: Why is the Product Prices the same as the first Line Item?
                  philmodjunk

                  You should have a price field in line items that looks up (copies) the current price from Pricing_Supplier_PO_Invoices. This will require that the correct value be entered or selected in both _fk_product_id and g_companies_id.

                  • 6. Re: Why is the Product Prices the same as the first Line Item?
                    starstuff

                    You should have a price field in line items that looks up (copies) the current price from Pricing_Supplier_PO_Invoices

                    i have these set up but the price is not changing per supplier

                     

                    This will require that the correct value be entered or selected in both _fk_product_id and g_companies_id.

                    i think its the g_companies_id (global) im having problem with, i did not actually set a script to update g_companies_id based on this thread

                    http://forums.filemaker.com/posts/838750c589?commentId=332465.

                     

                    is there another way to have different product prices per supplier without using any global field?

                     

                    UPDATE:

                    Progress after adding the global scripts for OnRecord and OnSave! :) Now the user selects the company and the price follows.

                    still i would like to know - " Is there another way to have different product prices per supplier without using any global field? "

                     

                    Also,

                    When i change the company on the same invoice and commit the record , the prices on the LINE_ITEMS portal is not updated to the changed Company prices.

                    How to update all the products prices automatically when the company is changed?

                     

                    Thank you again!

                    • 7. Re: Why is the Product Prices the same as the first Line Item?
                      philmodjunk

                      Using a global field to "reach through" an intermediate table and match to records in a third table is pretty standard and not complicated to set up.

                      If you are using FileMaker 12 or newer, an alternative is to use ExecuteSQL in your auto-enter calculation.

                      A third option would be to use a script to perform a find for the correct pricing record, copy the value to a variable, return to the original layout and record before copying the value from the variable to the field, but this is much more complex an approach than using the global field.

                      To change all the looked up prices after changing suppliers will require a script--this could be run from a script trigger on your company id field. If you are using the looked up value field option, you can use Go to Related Records to pull up a found set of this order's line items on a line items layout and then it can do a relookup specifying either the global field or the product id field. If you used an auto-enter calculation, do the same initial steps, but use replace field contents instead of relookup.

                      • 8. Re: Why is the Product Prices the same as the first Line Item?
                        starstuff

                        Thank you for the options, i bookmarked it for future reference.

                         

                        i tried the update prices and i cant get it to work, i was able to use replace but it replaced every records on every invoice, are my configuration correct? see below

                         

                        the PO_LINE_ITEMS::price is a Text field, with auto-calculation to get the data from PRICING_SUPPLIER

                        i added the script in PO_INVOICES::_fk_company_id  - OnObjectModify

                         

                        the script is

                        ---start

                        Go to Related Record [Show only related records; Match found set;

                        From table: "PO_LINE_ITEMS";

                        Using layout: "PO_LINE_ITEM_Detail (PO_LINE_ITEMS)" <--- form view of the Line Items

                         

                        Replace Field Contents [PO_LINE_ITEMS::_fk_product_id]

                        ----end

                         

                         

                        Also,  i noticed that the g_company_id is not in sync with the _fk_company_id,

                        meaning if i change the _fk_company_id, the g_company_id retains the old "ID" 

                        but if you enter another field, say adding a product or change the company to the same company,

                        then the g_company_id will change, is this the expected behavior? or something is wrong here?

                         

                        and if i would like to update a product price to a new price on the LINE_ITEMS table without going into the PRICING_SUPPLIER table, how do you do that?

                        example the old price for Apple is 100 on the PRICING_SUPPLIER table

                        the new Invoice price for Apple is 200

                        on the LINE_ITEMS, i would just change the 100 price to 200 price and the prices on the PRICING_SUPPLIER table would get updated.

                         

                        Thank you!