Am I going toward the right direction?

[malagasy]

Hi,

I have a business selling lottery tickets by mail order.

Usually, when a customer contact me, he will define which draw he'd like to be part of, the package he wants (amount of games in package). From these 2 information, I will invoice my customer and allocate him a set of numbers already generated (I have 100's of sets).

I have created a database with 4 tables:

1/Customers - 2/Draws - 3/Packages - 4/Set of numbers.

When my invoice is done, it will get the required info from each of tables.

First, do you think I have done the right thing? I have really tried to simplify the system by creating 4 tables since all elements are independants.

Then, I have an important question. I'd like to avoid duplicate of allocation of set to 2 different customers.

EXAMPLE:

Package 1 is made of 10 games.

Package 2 is made of 20 games.

A set is made of 10 games. Hence, Package 2 is 2 sets.

I have Mr A ordering Package 1 for Draw 1111.

I have Mr B ordering Package 1 for Draw 1111.

I have Mr C ordering Package 2 for Draw 1111.

Mr A will be allocated set 1

Mr B set 2

Mr C set 3 and 4.

I want to make sure that for a same draw, Mr A and B wont have the same set.

How should I do this?

[comment]

1. I think you are missing an Invoices table.

2. When you sell a set, you need to set a field in Sets with the InvoiceID of the invoice. A set whose InvoiceID field is empty is available. A set with an invoice number is sold.

3. My question is, where do I find people to pay me for generating random numbers?

[malagasy]

I have a database with

Customers Table

Orders Table

Product Table

with their respective layout.

In the order, I have a field where I can select the product (only a few). A, B, C, D

Each day, no more than these 4 items can be sold.

I want some kind of solutions that will make sure if item A is sold on 01/01/07, it cannot be sold again that same day and the drop list should reflect that.

For instance,

Customer John buy item A on 01/01/07

when customer David call and order the same day, I want to make sure the only item viewable in the drop list will be B,C and D.

Hope you can help.

Thanks

[mr_vodka]

You can get a list of all items that have already been sold for a particular date, then us a method as stated in this post

[mr_vodka]

Also, here is a link to another Demo

[comment]

Didn't I answer the same question here?

[malagasy]

Hi,

A few months/weeks ago, I have posted a topic

http://www.fmforums.com/forum/showtopic.php?tid/183893 regarding the creation of a dynamic value list that will only show the remaining item to be sold for a specific date.

I have been trying everything but still can't achieve the desired result I am going mad on that thing...really !

I think my problem is 1/ in the relationship but still can't figure out what to do and 2. the option of the value list.

I have attached and highlighted the relationship that i think have a problem. Pretty much, what im trying to achieve is making sure a set of numbers is not given twice for a same draw date (hence the value list that would only show the ones left for a draw).

Hope you guys can help.

Thanks

Edited March 5, 2007 by Guest

[aldipalo]

There is no attached file.

[malagasy]

sorry...i forgot the attachement !

Is this can help, the value list is using the value from availableset:setId and only included values from orderline table

[mr_vodka]

The links on this post should help you.

[malagasy]

Thanks Mr Vodka for the link,

I tried to apply the nightwing method but still no success (I have no idea how to apply the dwindle method).

What am I doing wrong ? I guess it's from the relationships but still don't get it (I'm really new to filemaker and databases)

Regards

[mr_vodka]

I dont know what more can be done that is better than an example. There are three examples on there. Maybe you can go into more detail on where you are having issues with it.

[malagasy]

Ok,

I have changed some of the relationships and I'm pretty sure I'm extremely close from the result. I ASSUME the problem might come from a relationship between the setline table and the customer (or order) table but still...no success

Ideally, I want to be able to choose the set of numbers from the order layout (mainly focusing on making that thing work in the draw layout at the moment to make thing easier).

I have attached my file if someone feels like a Challenge and solve the problem.

Let me know if you need any more details

Edited May 15, 2007 by Guest
file removed at authors request

[mr_vodka]

So are you saying that on the Orders layout, in the draw details portal (OrderLine), you want the pulldown menus to only display those that have no already been selected in a previous row? If this is not the case, then please elaborate.

[malagasy]

ideally, yes, i'd like to select the sets in the draw details portal of the order layout but only for the relevant draw.

ie:

Customer order 2 draws, i will record these 2 draws in the portal

17/02/07 and 31/03/2007

next to it, i want a field that will display remaining set available for each draw (if 1 is already given to customer B on 17/02, i don't want to see it).

Am i clear ? Sorry if i don't -)

[malagasy]

I'm still trying to make that thing working but I obvioulsy don't get the basic principle so no luck as yet -( -(