Finance | Project Operations, Human Resources, ...

JOIN not binding?

(0) Share

Report

Posted on by KeithM

529

So I have joined several tables for a custom grid that an outside source created and added other relationships with no problems. Then I get to where I need to add a custom field from the LOGISTSPOSTALADDRESS table. So I add the relationship to the salestable where the RECID from LOGISTSPOSTALADDRESS joins to DELIVERYPOSTALADDRESS in the salestable. Yet I am still getting only the first record to populate. Am I missing something goofy?

All responses (7)

Answers (0)

KeithM 529 on at

Like (0)

Report

RE: JOIN not binding?

What would cause the datasource to not show in the query statement?
KeithM 529 on at

Like (1)

Report

RE: JOIN not binding?

Awesome.... so I don't even see the join for the logisticspostaladdress at all. I have it inner joined on the sales table. The sales table already had a relation to it within the table.

SELECT FIRSTFAST FORUPDATE * FROM SalesLine(SalesLine) USING INDEX TransIdIdx
WHERE ((SalesType = 4) OR (SalesType = 3) OR (SalesType = 2)) AND ((SalesStatus = 1))
JOIN FORUPDATE * FROM InventDim(InventDim) ON SalesLine.InventDimId = InventDim.inventDimId
NOTEXISTS JOIN FORUPDATE * FROM SalesDeliverySchedule(SalesDeliverySchedule) WHERE SalesLine.InventTransId = SalesDeliverySchedule.OrderLine
JOIN FORUPDATE * FROM EcoResProduct(EcoResProduct) ON SalesLine.ItemId = EcoResProduct.DisplayProductNumber
JOIN FORUPDATE * FROM EcoResProductTranslation(EcoResProductTranslation) ON EcoResProduct.RecId = EcoResProductTranslation.Product
JOIN FORUPDATE * FROM InventTable(InventTable) ON SalesLine.ItemId = InventTable.ItemId
OUTER JOIN FORUPDATE Name FROM EcoResCategory(Ref_EcoResCategory_SalesCategory) ON SalesLine.SalesCategory = EcoResCategory.RecId
Martin Dráb 230,900 Most Valuable Professional on at

Like (0)

Report

RE: JOIN not binding?

Aha, so you don't know how to get a query string and review you query.

You can see it directly in the GUI, in the debugger or write code extracting the value (and putting it to infolog, for example).

In GUI, right-click the form and highlight Form information and click Form name: SalesTable. This will open Form information form, where you can see the query string. Note that it's not SQL, it's a pseudo-SQL representation used in F&O.

The fact that you can get data from different SQL query is not very useful. It tells you nothing about the problem in the query that you're using in the form.
KeithM 529 on at

Like (0)

Report

RE: JOIN not binding?

In SQL is pulls up just fine on a particular salesid anbd is the proper company I don't have a query string. This is databound straight to the grid.
Martin Dráb 230,900 Most Valuable Professional on at

Like (0)

Report

RE: JOIN not binding?

Look at your current query string and review whether it looks like you expect. Share it with us if needed.

Also note that your SQL code returns orders from all companies (and partitions), while F&O filters that.
KeithM 529 on at

Like (0)

Report

RE: JOIN not binding?

I tried that and still getting just that first row with data. Would it have to be anything with the relation to the LogisticsPostalAddress table? I also noticed that they created a relationship already with an unusual name(last screenshot). I have a feeling those may be fighting each other. Plus it has some parameters that may be causing it ? Cardinality?
Suggested answer

Martin Dráb 230,900 Most Valuable Professional on at

Like (0)

Report

RE: JOIN not binding?

Your SQL code doesn't match your query in F&O. LogisticsPostalAddress is a date-effective table and F&O returns only currently valid records by default.

You'll need to think about your requirement. If you want the active address, fix your SQL to return correct data, consistently with F&O. If you want your query to include old addresses, change Valid Time State Auto Query of the data source to DateRange. See Date Effective Data Sources for Forms for more details.