OK, I created a relationship between the Contact table and the FLGX_Accounts table using TAXID.

I created a smartpart under the contact entity and in the smartpart added a datasrouce that points to the relationship and a datagrid that uses the datasource.

Everything works except no records show in the grid.

In other smartparts where the relationship links based on ContactID it works fine.

So how do I get the records for this relationship to show in a tab based on the TAXID relationship.

I feel like I'm missing something stupid.

Thanks, Russ..

you've got the relationships going back and forth both ways for the NON Contact ID link, correct?

Is there actual data in the database?

The Smart Part and data grid are Not reacting to the ContactID, correct....you've pointed everything at this non contactID relationship.....the data grid, etc.

I have a 1 to M relationship where the Contact table is the parent and the FLGX_Accounts table is the child where the link is based on TAXID.

Yes, the contact record I am using definatley has multiple records in the FLGX table.

The datasource uses the relationship I created., and the datagrid points at the datasource.

Thanks, Russ...

I would suggest that you run the SLX Profiler on the Web Server, then load up a Contact and open up this grid.

Then find the Statement that should've loaded the grid and see how many records it returned. More than likely it is returning 0 rows, but if that is the case, you will have the SQL statement that actually executed against the DB and will be able to take it apart.

Things to look on the SQL statement for:

- Security Joins

- Inner Joins for other associated Objects

Also, you may be able to find out from the Profiler if SLX or SQL failed to Parse/Execute the statement, plus the associated error message (I am thinking that this isn't your case as an error would've been reflected on the Client, but still something to check).

Thanks Raul, I definitely want to try this but am not seeing the profiler in the Slaeslogix app group.  Am I missing something.  Do I need to download and install this seperatley?

I was not the one that installed these, so I am not sure if it was installed or not.

Thanks, Russ..

NEVER MIND, I found it.

Thanks, Russ..

OK, here is the output from profiler

---------- Client SQL ---------
SELECT child1_.FLGX_ACCOUNTSID as y0_, child1_.ACCOUNTID as y1_, child1_.ACCOUNTNUM as y2_, child1_.accounttype as y3_, child1_.TAXID as y4_, child1_.TAXID as y5_ FROM CONTACT this_ inner join FLGX_ACCOUNTS child1_ on this_.CONTACTID=child1_.TAXID WHERE this_.CONTACTID = [DBTYPE_STR,"C6UJ9A0003MD"]
---------- Executed SQL ----------
SELECT child1_.FLGX_ACCOUNTSID as y0_, child1_.ACCOUNTID as y1_, child1_.ACCOUNTNUM as y2_, child1_.accounttype as y3_, child1_.TAXID as y4_, child1_.TAXID as y5_ FROM CONTACT this_ inner join FLGX_ACCOUNTS child1_ on this_.CONTACTID=child1_.TAXID  WHERE this_.CONTACTID = ?

Does this shed any light on why no records are showing? Why is the executed SQL and the CLient SQL different?

Thanks, Russ..

Russ:

  Is the TAXID field on the FLGX_ACCOUNTS table storing the Contact's CONTACTID value?

  Or do you have a TAXID field on the CONTACT table?

There is no ContactID in the flgx table, so I am trying to match based on taxid in the contact table and taxid in the flgx table.

If I just do a regular SQL query in SQL manager, records come back fine, so I must be doing something wroing in either the relationship or the datasource/datagrid in the smartpart. Just not sure what I am missing.

Got it.

If you pay attention to the SQL statement, it shows that you are joining from CONTACT.CONTACTID TO FLXG.TAXID.

You need to drop your Relationship and Recreate it, but make sure that you choose the Correct Fields on both sides (Source and Target table).

OK, I am very confused now.

I deleted everything and started from scratch.

I made TAXID indexed and an ID in the contact table.

I recreated the relationship and it is contact.taxid 1 to flgx_accounts.taxid M.

Still getting the same thing.

Here is the profiler capture again:

---------- Client SQL ---------

SELECT child1_.FLGX_ACCOUNTSID as y0_, child1_.ACCOUNTNAME as y1_, child1_.ACCOUNTNUM as y2_, child1_.accounttype as y3_, child1_.TAXID as y4_ FROM CONTACT this_ inner join FLGX_ACCOUNTS child1_ on this_.CONTACTID=child1_.TAXID WHERE this_.CONTACTID = [DBTYPE_STR,"C6UJ9A0003MD"] 

---------- Executed SQL ----------

SELECT child1_.FLGX_ACCOUNTSID as y0_, child1_.ACCOUNTNAME as y1_, child1_.ACCOUNTNUM as y2_, child1_.accounttype as y3_, child1_.TAXID as y4_ FROM CONTACT this_ inner join FLGX_ACCOUNTS child1_ on this_.CONTACTID=child1_.TAXID  WHERE this_.CONTACTID = ?

Any more advice?

Thanks, Russ...

When you create the Relationship, make sure that you choose the TAXID on both sides of the View.

I am certain that I chose TAXID and it is beings used on both sides of the relationship.

All I can think is that your TAXID property in App Architect is actually pointing to the ContactId database field. Thats what the profiler and your description is suggesting.

Thanks,

Nick