Creating a report from multiple tables

[MStritt]

Hello,

I need to create a report, that will require data from at least 4 different tables. It was mentioned that I need to use/create a database view. I've never done this. Is this what I need to do? Or is there another way to display data from multiple tables without creating a database view?

[MStritt]

It is a related list on the Account. I tried dot walking to account.location.state, but it's not an available column. There is a 'State / Province' field also on the Account table (customer_account), so I can only dot walk to the this field on the Account, but not the Account Location.

[Adam Stout]

What you are showing looks like a reference to account, not a related list.  Are there multiple accounts on the case?

On the customer_account table, I don't see an OOTB reference to account.  Is that something you added?

Perhaps you need to discuss where this data really is with the application owner or system admin.

[rajneeshbaranwa]

Can you check what is the relationship between Account and Location table ? This will help us in answering your query

[MStritt]

I created a database view, and I got records with the correct State/Province information. However, it's only displaying a certain number of records (cases). Only 128 are returned. If I activate Left join on the case table, I get tons more case records, but the State/Province information shows as empty.

[Adam Stout]

Since neither of these account fields is unique, you would get some type of weird result.  I think you need a reference on customer_account to location.  Then you can use a normal dot-walk.

In ServiceNow, if there isn't a reference to something you think should be, the data model isn't doing what you think it should.  You need to fix the model (add the reference) or understand that the model is actually doing. 

A view is not going to fix this logic issue.