Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.

What is FoxDataObjects?
FoxDataObjects (http://www.foxdataobjects.com) is an object/relational persistence and query engine for Visual FoxPro. It lets us develop persistent classes following object-oriented idiom - including association, inheritance, composition, and collections, and allows us to express queries using a simple Object Query language, as well as in native SQL. Basically the product automates the Data-Access layer of an n-Tier application, but more important, it maps our business object model to a relational model, connects our Business layer to any supported database engine and provides a simple API for object persistence and querying. In this article, we will re-create parts of the Tasmanian Traders (Tastrade) sample application, but in an n-Tier architecture to show some basic features of FoxDataObjects.

Part 4 - Working with Collections
In this article, we will learn how to work with To-Many relationships by using collections. In the second article, we have seen that in Object-Oriented analysis, there are To-One and To-Many objects relationships and learned how to work with To-One relationships by using single objects references. In the third article, we used To-One relationships for Address instances contained on Customer, Supplier and Employee entities.

Now, we will see how to work with To-Many relationships.

There two types of To-Many relationships:
Where one object instance in the left side of the relationship, references or owns one or more objects instances of the entity in the right side of the relationship. It could be an Order instance holding owned references to a set of OrderLines instances.
Where one object instance in the left side of the relationship references one or more objects instances of the entity in the right side of the relationship, and at the same time a given object instance of the right side references to one or more objects instances from the left side entity. As an example: one Customer instance holding not owned references to a set of Categories the Customer are related with, and at the same time, each category may reference one or more Customers.

One-to-Many relationships are implemented using a Collection of references. A Collection is an object used to manage a list of items (values or objects pointers). It allows iterating the Item list, exposes a Count property holding the number of items currently contained in the collection, and exposes methods to add and remove items from the list. In Visual FoxPro 8 and up you can use the native Collection base class, or use the more advanced fdoCollection class.
Many-to-Many relationships are implemented using a Collection of references on each side of the relationship, the equivalent of two mutual One-To-Many relationships. As an example, a Customer class may have a collection of Category items (categories the customer belongs to) and at the same time, the Category class has a collection of Customer items (customers belonging to the category instance).

To implement to-Many relationships in our business classes, we can use a Collection class, either by using a property in the class definition that holds a pointer to a Collection object, or by adding a contained Collection object into the class definition. In both cases, the Ownership flag for such a member, applies to the objects referenced by the collection object.
Tip: Normally, it is easier to work with contained collections because it ensures the collection structure is created at container instantiation.

Mapping One-To-Many Relationships
In the relational model, One-to-Many relationships can be implemented either by adding a foreign key column in the right side table (the table holding instances from the many side) or by using a link table (a table holding two foreign key columns, one for the left side and other for the right side of the relationship).

FoxDataObjects uses both methods based on the Ownership option in the Relationship tab for a Collection member. If the Ownership option is set, FoxDataObjects adds a foreign key column named Owner_ID in the referenced class table. If the Ownership option is not set, FoxDataObjects creates a link table definition with a least two columns (additional columns may be needed to keep collection ordering information).

First, let us create some class definitions that will use collections.

- Clear your objects instance by typing this in the Command window:


- Open your Classes.PRG file to edit:


- Add the following classes definitions:

DEFINE CLASS Order AS Container
    OrdNumber = 0
    OrdDate = {}
    Customer = .Null.
    Shipper = .Null.
    Ship_To = .Null.
    Discount = 0.00
    Freight = 0.0000
    Paid = .F.
    DeliverDate = {}
    Employee = .Null.
    Notes = ""
    ADD OBJECT Lines AS Collection

DEFINE CLASS OrderLine as Custom
    Product = .Null.
    UnitPrice = 0.0000
    Quantity = 0

DEFINE CLASS Shipper AS Custom
    CompanyName = ""

- Close your Classes.PRG file saving the changes.
- Open the Schema Manager tool
- Open the Tastrade.FDO mapping file

Your changes are documented and the Schema Manager shows new object-model items in Red.

- Click to expand the Order class node. You can see all members identified. Customer, Employee, Shipper and Ship_To members where mapped as single object references. Lines member was mapped as a collection of values (no other information was provided yet).
- Click to expand the OrderLine class node. You can see the Product member identified and mapped to . We will instruct FDO about the destination of those relationships.
- Click on the Customer member node (under Order class node)
- Click on Relationship tab in the right side panel
- In the Referenced Class dropdown list select Customer
- Click on the Employee member node. The right side panel shows the Relationship tab for the selected node.
- In the Referenced Class dropdown list select Employee
- For the Shipper member set the referenced class to Shipper.
- For the Ship_To member set the referenced class to Person
- Under the OrderLine class select the Product member and set the referenced class to Product
- Now we will define the Order/OrderLine entities relationship.
- Click on the Lines member node (under Order class node)
- Click on the Member tab in the right side panel

You will see the Lines member has been mapped as a Collection holding single values (like Strings or Numbers), but we want to use the collection to hold references to the OrderLine instances, so:
- Select Object Pointers in the Holding dropdown list (the very first option in the list)
- Click on the Relationship tab and select OrderLine as the Referenced Class
- Ensure the Ownership and Ordered collection options are selected (checked)
- Click on the ERD tab to get a view on how FDO maps the member in the Relational Model.

Because the Ownership flag is set, FoxDataObjects added a Foreign Key column in the right side table (OrderLine). The foreign key column named Owner_ID will hold the Object_ID value of the Order instances that owns the OrderLines instances.

Now, we are ready to play with our entities and classes. Close the Schema Manager and ensure your changes are saved. Try these commands using the Command window:

? oSession.IsConnected && .T.

At this point, we got a session object connected. Upon connection, by default, the session object checked the database schema and upgraded it automatically, recreating all the new tables and columns added into the Relational Model.

We will create and save some Customer instance to use in the sample.


Note we used oSession.CreateObject method instead of the native CREATEOBJECT function. Doing that, we get a full instantiated Customer instance, even empty Address and Contact objects have been created, so it is easier to work with a new Customer instance like:

oCust.CompanyName="Alfreds Futterkiste"
oCust.Address.Street="Obere Str. 57"

We will not fill all the properties, to keep this sample readable. Now we will just create a new order for the new customer.


We can create OrderLine instances and add them to the Order.

oLine = CREATEOBJECT("OrderLine")
oLine.Quantity = 34
oLine = CREATEOBJECT("OrderLine")
oLine.Quantity = 47
oLine.UnitPrice=oLine.Product.UnitPrice * 0.95

Note how easy is to add an item to the collection. Some basic functions should be implemented as events in the class code. Your instance may react to an AfterAdded event to recalculate some Order instance values, etc.

Saving our Order instance will persist the Order object with all of its OrderLine instances and the Customer object (because it has not been saved yet). All of this is protected under the same database transaction.

? oSession.SaveObject(oOrder) && .T.

Now, we will retrieve our recently saved order

? oOrder.Customer.CompanyName && "Alfreds Futterkiste"
? oOrder.Customer.Address.Street && "Obere Str. 57"
? oOrder.Lines.Count && 2
? oOrder.Lines.Item(1).Product.ProductName && "Guaraná Fantástica"
? oOrder.Lines.Item(1).Quantity && 34

We have seen how easy becomes to work with Collections and how FoxDataObjects solves the persistence problem by mapping the collection to Foreign Key or Link Tables as needed.


Bernardo Fourcade RunAhead Technologies was founded in 1999 by the Visual FoxPro Senior Architect Bernardo Fourcade. Fourcade graduated with honors in Information Systems Engineering and Information Systems Analysis at Universidad Tecnológica Nacional. He started developing software early in 1986 with FoxBASE+ and closely followed the language evolution, working with every single version to the latest Visual FoxPro version. Beyond his deep commitment with the FoxPro platform, he also expanded to Visual C++, Visual C#, Visual Basic .Net, ASP3 and ASP.Net technologies. A solid experience on Project Management and the whole software development process, from designing, coding, testing, to deployment and re-engineering, lead him to work for some of the most important software companies. Prior to RunAhead foundation, he performed for more than 10 years as Chief Technology Officer at an important consulting company, mainly focused on the Health Care and Television Broadcast markets, where he lead and managed the most advanced and challenging software development projects. Now, at RunAhead Technologies, Fourcade is leading a strong development and supporting team, committed with the high standards of the industry. You can contact him at info@runahead-tech.com and you can find additional information at http://www.foxdataobjects.com/About.htm.


Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: