Question (only for the advanced user!)

[Ninja]

I'm currently creating a large relational database to take the full workload of a warehousing and distribution company. I'm using FileMaker Pro 5 developer.

The software takes account off all orders/deliveries the company has to make, each on a particular date. Each of these deliveries is made for one particular manufacturer. When entering the data the user enters the 'ORDERS' layout, selects the manufacturer from a drop down menu (the list of manufacturers is stored in another file and the list appears through a lookup value list) the user then enters the delivery info including address, postcode, delivery date and items to be delivered. Given the list of items, item sizes and how much each manufacturer is charged accordingly the cost of each delivery is automatically calculated through values in the manufacturer file which again are looked-up through the manufacturer selected.

Later the user may want to produce an invoice for deliveries made between certain dates. This is where my problem arrises; some manufacturers are charged for every week, some every month, others are charged for Monday & Tuesday and for Wednesday, Thrusday & Friday! I have been trying to make the invoice cost calculation an automatic lookup. The user selects the manufacturer, and week number, these two values are entered into a field: MANUFACTURER-WEEKNO. in the deliveries file the same is calculated through the manufacturer and date fields. this then allows the invoice to add together all the related costs of all the delivereies within that week.

Problem being that some manufacturers need to be charged per month, or M&T or W,T,F. I cant think of a lookup method that can take this into account. I tried making the fields contain different data depending on how the manufacturer wishes to be charged, however this invloved relationships ontop of relationships and so could no be indexed and so could not be calculated. I am starting to think that i must create a different set of fields and a different set of relationships and invoice layouts for every invoicing period. This will make the program a mess and not as easy to use.

Can anyone think of a way around this problem?

Does anyone even understand what the hell i'm going on about?

[Ender]

Does anyone even understand what the hell i'm going on about?

Not me.

[Søren Dyhr]

So are the invoices only covering whats delivered??? If so would I share the itemlines between orders and Invoices.

These itemlines should have a delivery date field that should make a concatenate key with clientID ...since you're on ver. 6.0 must this new key be made by Edoshins Smartranges technique http://www.onegasoft.com

Now the invoice does allready look up a invoicing duration from the client data, which now will adjust the rangekey from the invoicedate. You might use the lookup last feature to produce the new invoice if you wish, anyways must a system be established to know which client should be invoiced on which day ...but as soon as the invoice is created is it filled with the lineitems belonging to that range of dates.

--sd

[transpower]

Just calculate an invoice date appropriate for each manufacturer.

[Søren Dyhr]

Just calculate an invoice date appropriate for each manufacturer.

Huh?

--sd

[Ninja]

ok, think i need to explain the problem more clearly:

The files are ORDERS, ITEMS, STOCK, MANUFACTURER and INVOICE:

the files contain the following data:

ORDERS: Manufacturer(from MANUFACTURER), Customer, Address, Delivery date, Cost (AutoCalc from MANUFACTURER costs).

ITEMS: ProductName (From STOCK), Quantity.

STOCK: Manufacturer (From MANUFACTURER), ProductName, Quantity

MANUFACTURER: ManufacturerName, Address, Costs (stock costs, handling costs, delivery costs), InvoicingPeriod (Daily, Twice Weekly, Weekly, Twice Monthly, Monthly).

INVOICE: Manufacturer (From MANUFACTURER), Invoicingdates (Where the problem arrises)

The invoicing period should reference the MANUFACTURER::InvoicingPeriod and given this should only allow a selection of an individual date/wekk period/week/month period/month. Given the period it should then lookup and total the costs of all deliveries for this manufacturer in this date period.

Problem being that making a concatenated key between INVOICE and ORDERS causes a problem, since the key must be different depending on the MANUFACTURER::InvoicingPeriod and this key therefore cannot be indexed.

The file ITEMS conatains no data on the costs, since the cost cannot be calculated for each item and can only be calculated per ORDER.

I think the main problem is that the company i am producing this for use a different invoicing period and costing method for every manufacturer and it is very difficult to take them all into account!

Hope someone can help with this. Thanx

[Ninja]

Ok, i tried using the Edoshins Smartranges technique at http://www.onegasoft.com, looks good, it creates the right range of values and i'm damned if i can completely understand how it works.

But i still have problems, if i set the start and end date fields, the daterange field is populated by the range in numberical (not date) format, if i use the lookup between this and my date fields it can't find anything because they are in different formats. If i set the date field to a number or text it works fine, but i can't expect the user to input 0789884 for the 31/10/04 (or whatever it actually is). If i create a calculated field which is equal to the date field but outputs a number or text it can't be indexed and so can't be used in a lookup.

Maybe my problem is that i'm using FileMaker 5, i have a copy of 6 on its way and hope that that will cure the problem.

[Søren Dyhr]

Problem being that making a concatenated key between INVOICE and ORDERS causes a problem, since the key must be different depending on the MANUFACTURER::InvoicingPeriod and this key therefore cannot be indexed

Hi again

This is a classic tunneling problem, but you can have the indexing to your heart desire - this way: The usual way is to make OrderID key for the Items, but you can actually make it tunnel by making an extra line to the key containing manufactorID and invoicingInvoicingPeriod from orders.

In the itemline should a checkbox be checked to acutally generate the Edoshinkey by the use of the autoenter modification time. This checkbox means the line is invoicable.

Furthermore does the checking pull a set of unique stockID's for each Itemline via selflookup to an unstored ValuelistItems( filtered by LeftWords( to give stockID's for a foreign key seen from the Stock file. the reasoning behind is that you might wish to pull 10 items for a line while you only kan lay your hands on say 5 ...so only the 5 availiable stockID's are pulled.

Next thing to do if such an discrepenacy occures is to dupe the itemLine and adjust the original line to whats shipped, while the remaining stuff have to be adjusted in the new line. This makes it easy to sort the portal seen in Order ...the unshipped goes to the top of the portal.

Now each time the Stock recieves new items, should a looping thru records in Items containing unshipped be performed, calling the subscript that makes new itemlines for the stuff still not availiable.

This subscript can actually been boiled down to two lines if you think of a selfjoin with allow creation of related, that transferes only the count of remaining via a Set Field to the new related you wish to create, and then lower the original (parent SJ) with the actually shipped.

--sd

[Søren Dyhr]

Maybe my problem is that i'm using FileMaker 5, i have a copy of 6 on its way and hope that that will cure the problem.

No its not the versioning that counts here Michails keys can be used with every version since 3.0 from 1995 - but I tend to use dynamicly related valuelist in my approach to make the sets of stockID's which calls for at least 5.0 ...so you're still in the clear!!!

Is there any point in me making a template to upload here??? If so should at least a days hessitation be required!

--sd

[Ninja]

I'm going to go try it in a minute, just gotta wait for an email, problem is, the computer im developing this on doesn't have internet access!

I shouldn't worry with the template at the moment thanks, but if i get in more trouble i'll post on here.

Cheers for the help

[Søren Dyhr]

I've made a quick ER on how I'm getting what you're saying please download!!

[transpower]

Just to elaborate. Set the calculated invoice date in a new invoice for the manufacturer. Up to and including that date, add invoice line items to that invoice. On the invoice date, after the final invoice lines are added, print and mail that invoice. Repeat.

[Ninja]

Problem with the ER is that Invoice looks up from ORDERS not ITEMS. Due to the way in which the manufacturer's charge the cost has to be stored in orders. One manufacturer charges

[Søren Dyhr]

Problem with the ER is that Invoice looks up from ORDERS not ITEMS.

With my drawing isn't it posible to see that the key between ORDERS and ITEMS is a multi(two)linekey first line is for the portal in Orders, second line is the tunneling of ManufactorID and ManufactorInvoiceDuration and perhaps the Deliverydate. I did it so because you wrote:

ORDERS: Manufacturer(from MANUFACTURER), Customer, Address, Delivery date, Cost (AutoCalc from MANUFACTURER costs).

In other ER's would info like these be a relation between ITEMS and MANUFACTOR ....would that be better??? Another question is, you are using the phrase "Looks up", which is a specific filemaker term. Remember that having info from other files is either a calc', a lookup, or a live relation say in a portal. which is which you're refering to???

Now 7 hours later is it still bothering me that you have Manufactor as a lookup in orders, when the stockID in ITEMS could provide you with an Looked Up ManufactorID, the rest of the Manufactor Data should remain unstored calc'fields, because you're witing this:

Each of these deliveries is made for one particular manufacturer.

...To design the ORDER layout, could you pull these unstored calc's outside the portal - which means that only first portal rows fields aka ManufactorName needs to be shown - only once. The other field like ManufactorID that can't be unstored is the autoenter for deliverydate, because it lives on the right hand side of the relations def' window for the SmartRangesKey as a Range to a date seen from INVOICE.

With INVOICE is it the same way we pluck the excess portal fields from first related record, while the rests stays inside. But why not investigate the roughly made template uploaded here!

--sd

Deliveries.zip

[Ninja]

Yes, thats exactly how i've done it now and its working perfectly. I just know i'm going to come up with a whole load more problems soon tho with other aspects of it, but atleast this is working now.

Cheers for your help

[Søren Dyhr]

I just know i'm going to come up with a whole load more problems soon

I'll stay tuned or at least try to ...alternatively txtmessege me at +4540423223 and we could exchange e-mail adresses.

--sd