3 Replies Latest reply on Oct 20, 2016 10:54 AM by edwardlscott

    Having trouble displaying only specific table rows


      I'm having trouble understanding what I am doing wrong.  I am importing data into a PO solution and some of the data from the import set is incorrect.

      It's such a problem that I need to see only PO's whose calculated totals don't match the imported totals.


      I have the following fields in my PO database:

      PONumber (calculation, result is number)

      POTotal (calculation, result is number)

      ImportedTotal (number)

      (and other fields that aren't important to the question.)


      I have created a table occurrence of PO called PO 2 and created a relationship between the two as follows:

      PORelationshipPO 2
      POTotal!= (not equal)ImportedTotal

      What I was expecting when I created a Table View based on PO 2 was a list of only PO's where the calculations did not match.  Instead I got a list of every single PO.

      Looking at my data I see I have alot of fields that do not match.  For instance, I have a PO that has a calculated total (POTotal) of 5635.19, but the value that was imported from our data (ImportedTotal) was 5334.35.  I only want those to show up and none of the PO's that match.   Even when I created a layout based on my PO table and insert a portal based on PO 2, It still shows every single PO.


      Can anyone tell me what I am missing here?


      Thank you,


        • 1. Re: Having trouble displaying only specific table rows

          This is a common misunderstanding of how a TO and relationships affect what data is displayed on a layout.


          A Layout will, by default, display all records from the underlying table. The relationship only affects how data is accessed from another table occurrence, not what records appear in your found set. To limit the view to only certain records, you must, either by script or manually, perform a find for the desired records.


          Optionally, you can use a list or form view layout and use a portal to a TO in order to show data where the relationship controls what appears in your portal.


          If you are familiar with systems that base a "form" on an SQL query, you can picture a TO as:


          SELECT * FROM datasourcetable


          No WHERE, no OrderBy, no "Join".


          You get the results of a WHERE by performing a find and the results of OrderBy by doing a Sort Records.


          This can take some getting used to as this means that your user can, without any programming on your part, produce very different found sets of records by performing their own finds as well as other operations that move records in and out of a given found set.

          1 of 1 people found this helpful
          • 2. Re: Having trouble displaying only specific table rows

            Thank you for that explanation.  That got me on the correct course of action.  By adding an extra calculation field to my database, I as able to find the records that don't match.


            Thank you so much,


            • 3. Re: Having trouble displaying only specific table rows

              If I'm understanding your need, I think you can more easily solve your problem with just a calculation. 


              Suggestion: Create a calculation field in the PO table such as:


              Fieldname: GoodOrBad? (calculation field):


              GoodOrBad? = If( PO_Amount_Original = PO_Imported_Amount ; 1 ; 0 )


              Place the calculated field on a list layout.  Do a Find in field for "1" if you want all the records who's original and imported values match (Good), or "0" if you want all the records that don't match (Bad). Searching on calculated fields can take a while in a large data set, but this should show you the record set(s) you want to isolate.

              1 of 1 people found this helpful