3 Replies Latest reply on Jun 15, 2015 10:57 PM by philmodjunk

    Question about Self-Join Match Field

    starstuff

      Title

      Question about Self-Join Match Field

      Post

      Hi All!

       

      im studying self-join (match field) and i cant figure how i got the result.

      The result :

      Im in the PRICING_SUPPLIER Layout/Table,  i was able to show only the suppliers from a drop-down value list using the "include only values from" COMPANIES_Suppliers_Only (TO) (please see attached screenshot of the tables)

       

      im thinking its becauses its connected to PO_INVOICES companytype (calc) = "Supplier", that it shows only the Suppliers in the value list,

       

      the COMPANIES_Supplier (TO) companytype = Indexed, By Value List, Required Value, Allow Overide, i used a value list to validate this.

       

      i like to ask why did the PRICING_SUPPLIER list only the Supplier, is it because of the PO_INVOICES?

      if so, then should i move the companytype in PRICING_SUPPLIER table  because im working on PRICING_SUPPLIER.

       

      Thanks All!

       

       

       

      matchfield.png

        • 1. Re: Question about Self-Join Match Field
          philmodjunk

          You left out a detail. You are listing values using the "include only related values starting from Companies_Suppliers_only" option, but from what table occurrence does the value list list its values?

          I don't see a way for the relationship to PO_Invoices to have any effect on what values you see in this value list unless you were listing values from that table occurrences.

          BTW, the relationship between companies_Supplier and PO_Invoices seems to have an unneeded match field as I don't see a reason to inclue a company Type field if you are already including the company ID as the other match field.

          • 2. Re: Question about Self-Join Match Field
            starstuff

            Hi PhilModJunk!

            Thanks for the help!

            You left out a detail. You are listing values using the "include only related values starting from Companies_Suppliers_only" option, but from what table occurrence does the value list list its values?

            the value comes from the Table Occurrence of  COMPANIES_Supplier using fields "_pk_company_id" and "display_name"

            and include only related values starting from COMPANIES_Supplier_Only 

             

            I don't see a way for the relationship to PO_Invoices to have any effect on what values you see in this value list unless you were listing values from that table occurrences.

            you are absolutely correct here! i removed the companytype and even replaced the calc result to Customers and it still shows only the Suppliers

            sorry for being a total newb, i was so focused on one record because i got the result that i wanted but when i added a new record, i cant see any value from the list.

            i changed the match to PRICING_SUPPLIER field c_suppliertype (calc = supplier) and the COMPANIES_Supplier field companytype and it seems to work

             

            i would like to make a self-join for COMPANIES, i would add 2 extra fields

            companytype = custom value list with Supplier and Customer, user choose from a radio button

            is_supplier

            is_customer

             

            How to make is_supplier field add supplier if user choose companytype supplier

            and is_customer fied add customer if user choose customer?

             

            BTW, the relationship between companies_Supplier and PO_Invoices seems to have an unneeded match field as I don't see a reason to inclue a company Type field if you are already including the company ID as the other match field.

            i set this up to match up only Suppliers not Customers from the COMPANIES table, is this wrong?

            Also, i based the database relationship to your design here,

             

            Thanks again!

             

             

            • 3. Re: Question about Self-Join Match Field
              philmodjunk

              Your value list should be listing all records from Companies Supplier that have the same company type as the current record.

              The relationship from Companies Supplier to PO Invoices already matches by Company ID. Presumably, Company ID uniquely identifies each record in Companies Supplier. Thus, if you removed company type from the relationship, you should still get the same set of related records in PO Invoices from a given record in Companies Supplier as this should only match to PO Invoices for a specific company. Thus, including the Company Type field does not affect how this relationship functions. It's simply an added complication to your design that you do not need.