8 Replies Latest reply on Oct 11, 2012 2:27 PM by SueFrost

    Data Issue in Unit Price

    SueFrost

      Title

      Data Issue in Unit Price

      Post

           Today's puzzler ...

           If this isn't a data issue, I'll ... well, I'll think of a suitable punishment.

           The Unit Price field on my Invoice Data table is not calculating properly for certain categories.

           Pricing information originates in the Products & Pricing table. It feeds Inventory and from there, Invoice Data. The Category field which is the relationship between the tables draws its values from the Category value list.

           On the Invoice Data table (via a portal in the Invoice Layout), some of the prices come up properly, others are blank or pull the first price I ever entered. Since some of the prices are right, I feel this must be a data problem. I just can't figure out where else to look. Since it's all driven off the Category value list, the data should be input identically across all tables.

           The code in my Unit Price field is:  If ( Use Tier 2 Price = "Yes"; Inventory Value List::Tier 2 Price; Inventory Value List::Unit Price). 

           I had this code working ... but changed something else (that I don't remember) and now it's broken:

            

           If (Use Tier 2 Price = "Yes"  or Qty >= Products Pricing::Tier 2 Threshold; 
           Products Pricing::Tier 2 Unit Price; 
           Products Pricing::Unit Price )

           What on earth have I done?

      BB_Relationship_Table.png

        • 1. Re: Data Issue in Unit Price
          philmodjunk

               Why does your relationship match by Category instead of by ITEM ID MATCH FIELD?

               Do all products of the same category get the same exact price?

               If you go to a layout based on Products Pricing, enter find mode and enter a lone ! into the category field, how many records are found if you then perform the find? If you find any records at all instead of getting the "no records found" dialog, then you have multiple records with the same value in the category field and this will keep your current relationship from working.

               Is this field: If ( Use Tier 2 Price = "Yes"; Inventory Value List::Tier 2 Price; Inventory Value List::Unit Price)

               defined in Invoice Data? Is it a field of type calculation or a number field with this as an auto-enter calculation? (if it's a calculation field, you have an additional problem when it comes to making changes to your prices and not allowing previously created invoices from reclaculating to show amounts that are not correct for the prices that were in effect at the time they were created.)

          • 2. Re: Data Issue in Unit Price
            SueFrost

            Why does your relationship match by Category instead of by ITEM ID MATCH FIELD?  Based on this post ... Conditional Value Lists - the Continuing Saga to get a conditional value list. 

            Do all products of the same category get the same exact price? - YES

            If you go to a layout based on Products Pricing, enter find mode and enter a lone ! into the category field, how many records are found if you then perform the find? - NONE

                 Yes, Unit Price exists on Invoice Data and is an auto-enter calculation. When I get it working as is, I'll add a condition to the calculation that if the Invoice is marked paid that the prices should stay as is.

            • 3. Re: Data Issue in Unit Price
              philmodjunk

                   I don't think anything is broken and you won't need to add that condition to keep the price unchanged--that's what is already happening and is usually the behavior needed to manage pricing in an invoicing system.

                   With auto-entered calculations and fields with looked up value settings in Invoice Data, the prices will not change when you change a price in the products pricing or Inventory value list tables. This is considered correct behavior as you want any price changes to only be reflected in any new records that you create in Invoice Data after the price change was made.

              • 4. Re: Data Issue in Unit Price
                SueFrost

                     That sounds good - but that's not what I'm seeing.

                     I do see price changes reflected in existing records and the prices are most definitely wrong. I've been fiddling around with it and now I'm seeing negative prices and values that don't appear anywhere in the database. Fun, eh?

                     Aha - Unit Price is actually an Unstored value. Type = calculation. I've just changed it to a Number field, Auto-Enter and a calculated value.

                     Now I'm back to pulling the very first price I ever entered. The same price is returned for every product. Argh.

                • 5. Re: Data Issue in Unit Price
                  philmodjunk
                       

                            Now I'm back to pulling the very first price I ever entered. The same price is returned for every product. Argh.

                       better check your relationships and the values in your match fields.

                       For tier 2 pricing, the current record in Invoice Data must match by the value in Item to a record in Inventory. There should only be one record in Inventory that matches, use a portal to Inventory on a layout based on Invoice Data to confirm that a related record from Inventory appears and that there is only one record in the portal. You then need to find that one record in Inventory and use the same test to check and see what records in Products Pricing appear. Again, you should see only one record in the portal and in this case, you can include the price field in the portal to check and see if that is the correct value.

                       In similar fashion, you can put a portal to Inventory Value LIst and check to see what records and prices appear in the portal.

                  • 6. Re: Data Issue in Unit Price
                    SueFrost

                         I found part of the answer ... the relationship between Products & Pricing and Inventory needed to point to the Inventory Value List instead of the Inventory table.

                         Inventory Value List is a second instance of Inventory and has a relationship to Invoice Data where Category = Category. So now I have this:

                         Products & Pricing::Category - - > Inventory Value List::Category - - > Invoice Data::Category

                         That gives me the correct price for a single tier of pricing.

                         - - - 

                         I get the concept that you're talking about with the portal on Invoice Data pointing to Inventory. Execution isn't working out. I've built a layout to show Invoice Data records and added a portal to Inventory. I should see a match on Category and Item. But I'm seeing more records and I think the problem is with how I'm building the portal. A portal is an illustration of existing relationships, isn't it? That would explain why I can't specify a filter (which records from Inventory show). But it works the other way around - on my standard invoice form, which contains a portal to Invoice Data, I see the appropriate items for each Category selected.

                         Oi. My brain is tired.

                         At one point, I'm sure I had this formula working in the Unit Price field on Invoice Data:  

                    If (Use Tier 2 Price = "Yes"  or Qty >= Products Pricing::Tier 2 Threshold; Products Pricing::Tier 2 Unit Price; Products Pricing::Unit Price )

                    • 7. Re: Data Issue in Unit Price
                      philmodjunk

                           Using the portals are to test to see if your relationship is working as expected or not. If you see only one related record, and the correct price in that record, then your relationships and data are sound. If not, you have some investigating to do to figure out where things are going wrong.

                           Off hand, I don't see why you need your relationships to link from Invoice data to Inventory value list to Products and pricing, all by the category field. I'd think you could link directly to products and pricing by category to access the tier 2 pricing that apparently is stored in that table. (I don't see why you need separate tables for your two price levels, these could be two pricinng fields in the SAME record instead.)

                      • 8. Re: Data Issue in Unit Price
                        SueFrost

                             HI Phil,

                             First off - thank you so much for your time! It is very much appreciated.

                             I've realized that my self-imposed deadline to have this up and running by the end of October is lunatic. I'm going to take some time, go through the training materials and get a better understanding of Filemaker. Then I'll probably be back with more questions :)

                             You're likely right about connecting Invoice Data directly to Products & Pricing - I'll give that try. Right now, I think I've come full circle - I'm getting mostly correct prices, but not all of them. Thing is, I don't see a data issue. And if it's a relationship issue, why would it only impact certain records?

                             Anyway, thanks again for your time and effort!

                              


                             Sue