Trying to import serial number from outside DB. 1

[damo82]

Hi all. I have a slight problem, trying to get a serial# into 4 DB's.
Let me explain a bit first. I was recently put in charge of managing Filemaker at work, but unfortunately my boss had already built up 4 seperate DB's, as there are four different types of Works orders we recieve. Had he just built one big one this problem would not exist, but them's the breaks.
So what i've done so far is build, for each DB, a seperate layout called "invoice" which automatically enters componant data, and charge prices, which we then email to the client once the job has been done.
For the invoice# I originally had:
DB1- "HardBoxSets", inv#=BWHB1 (then add one for new record)
DB2- "SoftBoxSets", inv#=BWSB1 and so on.

I would have been finished now except that my boss wants all 4 DB's to share the same "code". example: BW1 and so on.
(i hope i'm explaining this ok).
What I tried to do to rectify this problem, is create a DB called "serial",with a single field called "serial number" (funnily enough). I then told this file to create a new record on open. Then, on one the the four databases I created a button which performed this script:
if[Sbjobs:acked qty > 0]
Open file ["serial"]
Set field[Sbjobs::invoice no; Sbjobs::Invoice no = serial::serial number
close file ["serial"]
end if
If i were to put this on each of the four DB's it should always return the next new number. The problem is I am only getting a true or false return (0 or 1). Can anybody offer a solution or perhaps an alternate way of going about it. Any help would be much appreciated.
Thanks.

 

[JeanW]

I have a hard time to follow you on this.

Can you give an example of the outcome you want for each ID in each DB, so we can do a sort of reverse construction to give you a solution ?

HTH

 

[damo82]

Hey Jeanw, thanks for the reply.
What I need is for every invoice that we send to have a different invoice number but the same tag (BW). As there are four DB's I couldn't simply put a field on each generating a serial number as they would cross over.
What I ended up doing, although it is fairly messy, is putting a check box on each, called "checked", that runs this script:

if[boxjobs::invoice no.<1]
set field[boxjobs::checked; "checked"]
open file["serial"]
paste[select; boxjobs::invoice no.]
close file["serial"]
print[restore]
end if

As well as this I told the "serial" Db that on open, to generate new serial number, then copy that field.

What happens now is when the "checked" check box is pressed;

As long as the invoice field is empty (so as not to give an invoice with an existing invoice number, a new number),
Set the check box field "checked" as checked,
Then open file "serial",
Cut and paste the new serial number,
Close file "serial",
Then print the invoice.

I'm sure this is the long and tedious way to do it, and that my explaination was just as long and tedious, so if there is an easier way of doing it, the help would be much appreciated.

Thanks for listening, and I hope this explains my problem a little better. (never was much good explaining myself).

Damo.

P.S. As well JeanW, thanks for all the replies to all the other threads, you've certainly helped me get through alot of problems without me having to ask the question, which I think works for both of us, ha ha. Cheers.

 

[JeanW]

First let me see if I have this right:

You have 4 separate DB

1 for HardBoxSets
1 for SoftBoxSets
1 for XXBoxSets
1 for YYBoxSets

In each file is a invoicelayout to generate invoices only for the given file, invoice number starting with BW and a serial number that is ‘serial’ through the 4 DB’s.
This will give an invoice number in HardBoxSets : BW 1, the next one in the same file BW 2,
The next one, created in SoftBoxSets : BW 3
Then one in XX: BW 4
Then one in HardBox again: BW 5 etc…..

The problem you can have is when f.i. there is something wrong with printing, like paperjam or another problem, where you have to re-print the invoice. This will change the invoicenumber…..

Apart from putting the 4 DB’s in one file and work from there with a simple relationship, what should be the most appropriate in the long run, you could do something along these lines, it is still messy, but you have no other way….

Try this first with 2 separate files to understand the technique, before you do it in your live files
You need in your app a 5th file ‘InvoiceNumbers’,
Now for the technique. It’s a technique I use in FM 6, so substitute some FM6 names with the current FM7.
I hope I don-t forget something, I do it here 'by memory'...

Make 2 files, 1 Invoice and 1 InvoiceNr
We have to script the creation of records and will use some set field techniques.

Make fields in the Invoice file:
zk_timeBased_cti – calculation, text, index. This can be a concatenation of date and time or in FM7 a timestamp, which will give you a number for the current date and time. You can substitute this by any other unique value for each record, but this will do for the example.
InvoiveNr_t – textfield
InvoiceText_ai – autoenter BW
InvoiceTot_ct – calculation,text – invoiceText_ai & invoiceNr_t

In the invoiceNr file make fields:
zk_timeBased_t, text
invoiceNr, number
zk_constant_cni – calculation, number, 1, index

Back to invoice, make your relationships.
Here you need 2 relationships, based upon the zk_timebased_cti and zk_timeBased_t in the invoiceNr file
1 relationship that allows the creation of related fields and one that doesn’t has the allow creation.
Lets call the first one InvSerial2, with record creation allowed
zk_timeBased_cti::zk_timebased_t (allow creation of related records)
and InvSerial2.2 with creation not allowed
zk_timeBased_cti::zk_timebased_t

In the invoicenr file you need 2 relationships.
1 self join, Constant, based upon the zk_constant_cni field = zk_constant_cni ::zk_constant_cni
1 based upon the zk_timeBased_t field and the zk_timeBased_cti field in the invoice file.

For the scripts in this file:
Very simple, 1 script, Set Serial –
Goto Record last
Set Field (invoiceNr;Max(Constant::invoiceNr)+1)

Back to the invoicefile for the scripts.
New record script:
Set Field(InvSerial2::invoicenr;Max(InvSerial2::invoicenr))
Perform Script (External) (InvoiceNr(Set Serial)
Perform Script : Set Inv Nr

Set Inv Nr Script
Set Field (InvSerial2.2::invoicenr)

Now for the technique. In your first file you make a new record. With the allow record creation in the relationship, FileMaker will look in the InvoiceNr file for a matching field. There is none, so FileMaker will force that and will create a record with the matching value (timeBased value).
Due to the forcing creation, a autoenter will not be validated, so we need a script to make a serial number that act as a serial auto-enter, hence the second script.
Back to your first file, were you have created a record, the set field scripts will enter the created numbervalue in the external file and concatenate the values to give a ‘unique’ invoicenumber.

Once you understand the technique, you can follow the same way in your ‘live’ application, and make this in every single file, related to the the one invoicenrfile.

It’s not an ‘ideal’ way, but it will work, and it gives you an idea what you can do with relationships.

If problems, feel free

Happy FileMaking

 

[JeanW]

Don't forget to create your new records WITH the script.
Therefore, put a button on a layout to activate the script.
Maybe you will have to find a way to de-activate the creation of records true the menu items or with Ctrl + N, but that's another story.

HTH

 

[damo82]

Thanks heaps Jeanw, you helped alot.
Damo