Home

Macros and Programming

 

CardboxForumsMacros and Programming > "Complex Database"

Complex Database

Current user: [none]
Register / Log In · Help

Posted By Post

Jamie

13-Jun-2006 08:31

I'm trying to create a fairly complex database which includes one to many relationships. After some time learning about Cardbox it appears there's no easy way to do this, anyone have any idea? For example I have a record for a customer and a number of transactions relating to that customer.

Also, is it possible to use a macro to change the list of items in a drop-down list?

Hope someone can help.

Thanks, in advance.

Charles Welling

13-Jun-2006 12:08

The solution to this problem is built-in: relational searches (see manual):

Create two databases: one containing the customers including a unique customer ID and one containing the transactions, including the same unique customer ID.
You can link both databases using this ID. The basic concept of this method is that you open both databases and you search for a certain customer. Cardbox retrieves the customer record and takes the ID (could be any kind of number or word, as long as it is unique to make sure Cardbox links the right records) and it searches for all records having this ID in the transactions database. The result is one window containing the customer data and a second window containg all transactions.

The same could be done vice versa.
This macro is an example of how to open the transactions database from the customer database, assuming:
the databases are "customers" and "transactions"
the server is called "cardbox"
there is a default profile in transactions called "search", without a password
The macro tries to activate "transactions". If it succeeds it does nothing else.
If it fails it opens "transactions", sets up a relational link, tiles both windows and returns to "customers".

This example macro will only work correctly if you always use it to open the transactions database. If you open "transactions" manually, make a selection and then use this macro, no link will be set up.
Of course this macro can be altered to provide for all possible circumstances.

Charles

on error resume next
Activate "transactions"
if err.number <> 0 then
OpenFile "cardbox://transactions",,"search"
Activate "transactions"
end if

if SelectionLevel = 0 then
SelectRelational "CUSTOMER_ID","customers","CUSTOMER_ID",cbxRelationalUseAllTermsOR+cbxRelationalUseIndexedOnly+cbxRelationalMatch+cbxRelationalBlankFieldMatchesNothing
end if

Activate "customers"
Windows.Tile

Jamie

13-Jun-2006 12:56

Great, thanks very much, that should do the trick!

Any ideas about my other question; using a macro to change the list of items in a drop-down list?

For example, I wish to create a new transaction based on a customer already in the database. So I'd like to have a drop-down list with all my customers in it so I can select one.

Thanks,

Jamie

Charles Welling

13-Jun-2006 14:30

As far as I know there's no way of changing the format file by means of a macro.
But you say you want to add a new transaction. If you set up two databases, the transactions database will only contain the customer ID. You'd have to select the right customer from a list of (possibly) nondescriptive ID's, which is prone to mistakes.
Why not do as follows:

- Have both databases open (you can use the previously described macro for this).
- Select a customer, or add one if necessary. The Transactions window will show the current transactions.
- Use the macro from the example below. It has to be activated from the Customer database (you are already there) and using a button saying "add transaction" would be a logical thing to do.

The macro takes the customer ID of the current customer (remember you don't copy the entire customer data to the transaction, just the ID), switches to the Transactions database, and adds a new record with the correct ID already filled in.

You could have a "list view" in your Customer database, with just the customer's name and ID on a single line, to serve as your drop-down list. Put the button in the heading and you're done.

Charles

scrap(1)=FIELDS("CUSTOMER_ID")
Activate("transactions")
set rec=database.NewRecord
rec.FIELDS("CUSTOMER_ID")=scrap(1)
rec.Save
LastRecord
EditRecord

Charles Welling

15-Jun-2006 12:47

I reread my reply and realized that that last macro could be simpler using AddRecord instead of NewRecord:

scrap(1)=FIELDS("CUSTOMER_ID")
Activate("transactions")
AddRecord
FIELDS("CUSTOMER_ID")=scrap(1)

Quick Reply

Please log in or register before trying to post a reply.

 
© 2010 Cardbox Software Limited   Home