Need Formula Magic 2

[lordb]

Hello Tec-Tip experts,
I need some Formula Magic for a very simple report. Running CR 8.5 working a FoxPro Database. Report parts and fields follow:

GH1 Order No.

Details ItemDes.(string) ShipQty(Number) Location(string)

Challenge: there are many cases were there is no Location data (Inventory Shortcoming). No Location data leads to that particular line of data not showing up on report, and the item not getting shipped. Not good. I have gone through all of the IsNull threads with out success. Following {?Location} is one that I tried from synapsevampire to help with the discussion.

If isnull({Location.field}) or trim({Location.field}) = “” then
“No Location Data” else {Location.field}

Seems like it should work...but no such luck. Thinking this is a database quirk that needs another approach for a blank field and a corresponding not return of associated data fields in CR. All ideas and suggestions appreciated. Thanks Byron

 

[lupins46]

This is likely to be an issue to do with table joins.
In principle you need to ensure you have a join type which selects all the records you want to report on, irrespective of whether there are matching records on the other tables.
(This is typically a left outer join).

But I know nothing about Foxpro so can't give you any more detail.

 

[lordb]

Lupins46, thanks for the quick reply. If time permits, could you elaborate on what a left outer join is, or point me in the right direction to learn more. I've seen "join" disscussed in other threads...and had the big blank "what's that?" form over my think head. Will try a key word search. Thanks again for the reply. Gives me a place to start. Byron

 

[kchaudhry]

Left Outer [=(+), *=] join

For this example, each join is specified in the WHERE clause of the SQL statement.
The result set from a Left Outer join includes all the records where the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table whose linked field value has no match in the lookup table. For instance, you can use a Left Outer join to view all customers and the orders they have placed, but you also get a row for every customer that has not placed any orders. These customers appear at the end of the list with blanks in the fields that would hold order information:

Customer Table
Customer ID Customer Table
Customer Name Orders Table
Order Amount
52 Allez Distribution 25141.50
53 BG Mountain Inc. 19164.30
53 BG Mountain Inc. 1683.60
57 Hansen MTB Inc. 15716.40
58 La Bomba Bicicleta 1956.20
60 Mountain Toad 24580.50
62 SFB Inc. 7911.80
63 Sierra Bicycle Group 19766.20
63 Sierra Bicycle Group 12763.95
64 Sierra Mountain 8233.50
54 Bicicletas Aztecas
55 Deely MTB Inc.
Left Outer and Right Outer joins are handled differently in the SQL language from other join types. If the database is accessed through ODBC, the Crystal Query Designer will use ODBC syntax in the SQL statement. If you are connecting to an SQL database directly (not through ODBC), the Crystal Query Designer will use a syntax native to the database. For complete information on what an Outer join looks like in an SQL statement, refer to Microsoft ODBC documentation or the documentation for your SQL database.

Hope this helps!

Kchaudhry

 

[lordb]

Thanks much kchaudhry. But what you have passed is beginning to make some sense. Found the join types in the Database->Visual linking expert. Gave left outer a go and all that returned form my Order report was one line of "No Loc Data". This gives me the feeling that left outer or some version of the join is the answer here. Further info is that the FoxPro data base is accessed through ODBC. I'll keep on it...got to be a secret here. Thanks Byron

 

[lordb]

To Lupins4 and Kchaudhry, got it! Left outer Join is the Magic needed. Once I got my tables linked right and the left outer selected, all is good now. Thanks of the quick and accurate turn on my query. Byron