Portal - New rows creates new data records....

[mediaman69]

Hello everyone,

I have got a specific problem with my customer database (customer.fp5)

At the top, I've created fields for personal customer data like first name

last name, city etc. Below that I've created a portal which I want to use

for the items the customer has bought.

For the portal, I've created a relationship on itself with last name ::last

name. In the portal itself, I've placed the fields :

Prod.-no. | Products purchased (name of prod.) | Cost of prod. | Creation date|

My problem is that it creates multiple records for one customer name every

time I add a new row. So if I start with the second row, it also creates

a second data record for the same customer. How can I achieve that this is

not done ? I want one customer name and multiple products in the portal

without duplicated records for the same customer.

Do I have to create another relation for this ?

Thanks in advance for your answers and ideas.

Detlev

[jasonwood]

I gather you're using a self relationship? Thats not gonna work. You need a separate file. If these are the only files you have, you could create a separate file (eg: itemspurchased.fp5).

If you are also using your database for invoicing, then you'd be better off doing this automatically. Put a stored calculation field in your invoice line items that equals the client number in the related invoice. You can then relate to the line items from your contacts database using the client number.

By the way, you should almost always use a unique serial number to relate records (like your Client Number), rather than something like a last name. It is possible that two people can have the same last name, and if you ever change the spelling of the name, the links are broken.

[danjacoby]

In addition to your current problem, you're also going to have a problem when you get two customers with the same last name.

You should create a key field based on more than just the name -- in fact, unless your customers are individuals (as opposed to, say, companies), you should use the name for the relationship at all. Because if you're working with companies and the name of your contact changes, you're screwed.

[mediaman69]

Hi Jason,

Thanks for your answer. Yes, I was using a self-relationship but I also

tried to combine it with my products database before. And when I did a

relation (creating records allowed) to that product database, it created

new records in the products database instead of just adding rows to the

portal. In fact, I have used a unique key in both databases called

"Data no." .

So, for example, I have three products solo ads, top sponsor ads and

classifieds. Each product has this unique "Data no." . But when relate

the Data no. from the customer db to that product db data no. , it creates

new records just when I add rows to the portal.

My last try so far consisted of creating a copy of the customer database

called c2.fp5 . This was used as a container for all the new records which

were created when adding rows.

I also have problems when I want to add up those prices from the portal

into a total $ sum for each customer.

I'm going to attach the files so it is easier to see what I mean....

I'm always getting a little bit confused with all those relations which

have to be created.

Thanks ,

Detlev

P.S.: No, the database is not used for creating invoices.

customersFP5-Files.zip

[jasonwood]

Hi Detlev,

You have a bit of a misunderstanding about how portals work. This is quite common for beginners. Unfortunately you cannot use a portal to create a list of multiple existing records, at least not directly.

A portal shows records that relate based on the key, so unless you want to have a "clients" field in your products database, which would list all of the clients that have purchased the item, you will not be able to create a relationship from clients to products.

Typically, when you want to gather information in a portal, you create a new database, commonly called "Line Items". You normally don't use this database directly... only through the portal. The user doesn't even need to know it exists.

This database would contain a client number to relate to clients, a product number to relate to products, and a number of lookup fields to gather the information about the product (and perhaps the client as well).

With this database set up in a portal in your clients database, you can add products to the list by entering the product number, which will cause the other fields to be looked up. This creates a new record in "line items", which will automatically contain the client number (otherwise it wouldn't show up in the portal).

An added benefit is that you can now create a similar portal in your products database, which relates to the line items file based on the product number... and now you can see which clients have bought that product!

[mediaman69]

Hi Jason,

again thanks for your answer. That was exactly what I wanted to know because

I wasn't sure if a third database would be needed between the customer and

the product database .

Now I know ....;-)

Detlev