Creating multiple parallel invoice number sequences

[FysiOlsen]

Hi.

 

I have created a database with clients, treatment records and am now trying to set up an invoice part.

The invoice part works fine with invoice numbers  in sequence (10001, 10002... etc) created by a number field with auto-enter serial.

 

My two  challenges is:

 

1)I need to set it up so that every user/account (at the time 3 or 4) has it´s own line of invoice numbers.

Therapist 1 : 10001, 10002...etc

Therapist 2: 30001, 30002...etc

Therapist 3: 50001, 50002...etc

 

Each therapist is self-employed (I don´t know if it is the right word, but I guess you can understand). Therefore, due to tax reasons, they need their own separate sequence of invoice numbers. For the same reason each sequence must be consecutive.

 

2) To eliminate problems with invoice number sequences, for instance if a therapist by mistake click on the button "create new invoice" I need the invoice number to be assigned when the invoice is printed and not when "create new invoice" is clicked.

I can´t just use "on commit" instead of "on creation" with the auto-enter serial, because, as it is right now, I create the invoice lines in a portal, and I guess it causes problems with the relation to the "patientId" field.

 

Both problems would not arise if there were only one therapist and the therapist did not accidently click on "create new invoice", but unfortunately the databse is not being used in a perfect world

 

The database is created so that the user has to log in using their own initials, for instance TK.

 

I have 4 tables (related to this part):  "Patient", "Faktura" (invoice), "FakturaLinier" (invoicelines), "Produkter" (products). They are related as you can see in the attached picture.

 

I guess I can create the 3 or 4  invoice numbers sequences with a calculation, but unfortunately I am not skilled enough yet to figure out how it should be done.

 

Help with some details will be very much appreciated as I am quite new to FileMaker.

 

Thanks!

[comment]

Can you clarify the purpose of the separate sequences? It's not a simple task to achieve reliably in any case, so knowing the constraints would be useful. Especially, if each sequence must be consecutive - or would it be sufficient to have something like:

Therapist 1: 10001, 10004 ...
Therapist 2: 30002, 30006 ...
Therapist 3: 50003, 50005 ...

[FysiOlsen]

Hi comment.

 

Thank you for your post. I just edited my original post as I think my two challenges must be adressed at the same time.

Therefore I clearified your questions in the the original post.

If it causes some confusions, I apologize

[comment]

First thing, you should have a table of Therapists and each invoice should be related to the relevant therapist record.

 

 

Printing an invoice MUST be a scripted process, which would do (roughly) the following:

 

1. See if the invoice already has an invoice number assigned to it;
2. If not, [a] get the next invoice number from a field in the therapist's record and set the invoice number to it and raise the next invoice number in the therapist's record by 1;
3. Print the invoice.

This is not the only way to do it, but I believe it's the simplest. Alternatively, you could have a self-join relationship count all previously printed invoices and add 1 to that.

 

 

Note that you need to keep the serial InvoiceID and use it (and only it) as the matchfield to the line items table.

[FysiOlsen]

I think I roughly can see where you are going, but it is a little difficult for me to create the steps to get there, but I am trying.

 

I have created a new table "Behandlere" (therapists) and, for a beginning, 4 fields:

"BehandlerId" (TherapistId) number field with auto-enter serial

"BehandlerFornavn" (TherapistFirstName) text field

"BehandlerEfternavn" (TherapistLastName) text field

"BehandlerFakturaId" (TherapistInvoiceId) number field (Later on I need this field to create the invoicenumber printed on the invoice...right?!)

 

Now I need to somehow relate "BehandlerId" with the "Faktura" (Invoice) table (I guess by creating a relationsship between "BehandlerId" and a foreign key "BehandlerIdFk" in "Faktura" table?! and how do I automaticly identify the logged in user/account with the right therapist?

 

Is it somehow right until now?

[comment]

Your Therapists table should also have an AccountName field (i.e. their login name), validated as unique. In the Invoices table, define a CreatedBy text field and set it to auto-enter Creation: Account Name. These two fields can be used to relate the two tables (subject to ... see below).


As I said earlier, this is not so easy to set up reliably. There are many ways this could go wrong - esp. if your users are as users will be... so you need to consider a lot of what-ifs and close the gaps as well as you can.

This is why I am hesitant to give you an exact step-by-step. For example: suppose that for some reason, user "TK" needs (or wants) to change their login name; this will break the link to all their previous invoices - and if their privilege set allows them to view only their own invoices (as it well should), they won't be even able to see them. So if you anticipate this possibility you must create a more complex setup.

[FysiOlsen]

Hi comment.

Thank you for the clearification.

Right now I am pursuing a suggestion I got from PhilModJunk in http://forums.filemaker.com/posts/c6aa5f9f0c?commentId=260572#260572

as it was a little bit easier for me to handle. Unfortunately I still can´t get it to work.

 

You are more than welcome to join in (I can see you also have a profile there). Your input is very much appriciated!

[comment]

You are more than welcome to join in (I can see you also have a profile there).

 

Yes I do, but the administrators over there had made it quite clear to me that if I didn't like the way they run it I was welcome to leave, and I took their advice.

[Lee Smith]

Their bad. What the h e l l were they thinking. Never mind, they weren't.

[comment]

Well, it was a good lesson in how they think. Not of me, but of their product (the forum software, in this case).

[Rick Whitelaw]

If I were a person trying to decide whether or not to purchase FileMaker and stumbled upon that forum, I wouldn't make the purchase. Although the forum software has had some minor improvements, it's just bad press for FM Inc.