6 Replies Latest reply: May 15, 2014 10:07 AM by Grant Perkins RSS

    Identifying Lookup Output Values with no Input Match

    Diane Miller

      Is there a way to identify Lookup field output values that have no input match?

       

      I am doing a reconciliation to a client's inventory and they sometimes show accounts as listed with us when we don't have them on our system. I have been doing my work in Access but think I can make the reconciliation much more efficient using Monarch for everything if I can just figure out how to do this one thing.

       

      Thanks

        • Identifying Lookup Output Values with no Input Match
          Data Kruncher

          Yes there is a way Diane. You can isolate those records by building a filter similar to:

           

          IsNull()[/CODE]

           

          Or if your Monarch version doesn't offer the IsNull function:

           

          Len()=0[/CODE]

           

          Does that help you to move ahead?

            • Identifying Lookup Output Values with no Input Match
              Diane Miller

              This returned anything with out a value in the output field which I already had.

               

              What I really want is to identify the accounts that are in my output field that are not in my report field.

               

              Example:

              Accounts In Report             Accounts in Lookup Field            Lookup Output Value

              12345                               12345                                     $45.00

              45267                               45267                                     $100.01

                                                     98642                                     $56.77

              67431                               67432                                     $44.02

              87632

              97841                               97841                                     $66.00

               

              I can filter and find all accounts that are in the report & in the look up field & get 4 results.

              I can filter and find all accounts that are in the report but not in the look up field & get 1 result.

               

              I want to find account # 98642 which is in the lookup field but not in the report.

               

              I appreciate your help.

                • Identifying Lookup Output Values with no Input Match
                  Diane Miller

                  Looks like my example scruntched up. I hope this will make more sense.

                   

                  Report Account   |   Lookup Input   |   Lookup Output     

                  98642                 |   98642            |   $45.00     

                  45267                 |   45267            |   $100.01     

                  -


                                  |   98642            |   $56.77     

                  67431                 |   67431            |   $44.02     

                  87632                 |   -


                             |   -


                        

                  97841                 |   97841            |   $66.00

                    • Identifying Lookup Output Values with no Input Match
                      Olly Bond

                      Hello Diane,

                       

                      Monarch allows you to carry out LEFT OUTER joins from your main table to external lookup tables. This means that it will fetch the data from the external table only for those rows that exist in your table.

                       

                      You're looking for a RIGHT OUTER join, and the only way to do that is to swap the two tables around. Open your lookup source as your main database in Monarch, then perform a lookup to your data.

                       

                      Once you do this, the filters Kruncher suggested will help you find the records you need.

                       

                      Best wishes,

                       

                      Olly