I want to modify my field

[Mats]

Yes I already have in mind not to keep doublet files as LaRetta told me this I now use Your last db as is and only switched the Invoice file in it as I had made some layout changes to it.

The "Z" reading is the end of the day results/report from the cash machine (not itemized) So it will have a maximum of 365 records a year with 4 fields each. Date, Net, VAT and Gross

[Fenton]

So, how are you getting that end of the day result? That is what you'd create and enter in VAT_Status, one record a day. How and when it's created is the big question. And what format is the file from the cash machine?

By not using my Invoice file, you probably broke the external calls in the VAT_Status "Update" script. They were broken in the files you uploaded. So I fixed them. Now you've broken one of them again.

One step is a Perform Script [External, Invoice.fp5], calling a "Show Al <--VAT" script in Invoice. If it's not there in Invoice, the external call will call some other script, with no warning.

If you're going to exchange files with someone who's building operations between files, you have to be careful not to break them. A Layout can be easily copied and pasted from one similar file to another; try it, it's easy. Cross-file scripts and relationships are not so portable, and much harder to fix.

[Mats]

Hi Fenton,

I'm learning and learning here. Thanks

The Z report (End of the Day Takings) will be entered manually as they just comes out on the receipt from the cash register. But I cant put the Takings in the Invoice or Purchase file. Do you suggest to enter them directly in to the VAT Status file? I think I will prefer to make a new file Named Takings. That will give me something to do as well,, hehe

[Mats]

Did you give up on me Fenton? (I don't blame you)

[Fenton]

No, not exactly. Someone in Japan wanted me to build a job-recruitment-placement system right away, ie., a job.

I was wondering whether you'd succeed on your own, and where you'd get stuck if not; we developers are a morbidly curious lot :-|.

You have 90% of the structure already. The Z date imports would be just the same as the others, except you'd be matching dates instead of serial ID's; a date can be a unique key if you only enter 1 record per date, as you said you were doing for Z's.

The z dates and numbers would just need their own fields, just like the others. You would have to wrestle with the Import Matching dialog again; check "Update Matching Records," match the <=> double-ended arrow on the Z_Date field, and check "Add Remaining Records."

You need full found sets of z-dates (but only z-dates, you don't care about the others) in both files at Import time; or a method to isolate only the newer dates to bring in. That would be slightly faster in operation, but more work to implement. At a maximum of 365 to match against, matching should be reasonably fast, at least for a year.

[Mats]

Hi,

Well I'm back I have done some work on the db since my last post and therefor have new problems to solve

My accountant advised me to always use Chart of Account numbers which is a fixed transaction code for each transaction. And they will also match the codes used by the accounting program used by the accountant.

So I have crated a new file named "Chart of Accounts" holding all the account/code numbers. Then in all the files holding transactions: (Invoice, Purchases, Salaries and Takings) I will have a field for the Chart of Account No. (I have started in the "Takings" file)

Then I need a "Chart of Accounts Statement" file, which record all the transactions by account no.

Example:

1. I create a new record/transaction in the Takings file

2. I enter the date

3. I enter a Bar Sale. Then the account number will be set to account

no. 3200 (relation or lookup? Chart of Accounts file)

4. The VAT for drinks are set to 15% in this case so in the VAT field I

will have a calculation "Bar Sale x 0.15" or maybe I will also have a

VAT rate field in the Chart of Accounts and make a calculation from

there (makes things more easy if the VAT rate change I only have to

change it there)

And I prefer if the "Chart of Accounts Statement" file records the new transactions without import function, but automatically as a new record is created. But thats just what I would like, and maybe not a good solution?

I attach my db and a small doc how I would like the "Chart of Accounts Statement" file outcome to be.

I will also add a VAT field for each of the chart of account number's so that also can be a lookup.

I forgot the actual question How will I make the best relationship and lookup between the i.e. "Takings" and "Chart of Accounts" files? Auto enter serial or based on something else. And how would I make the solution between i.e. "Takings" and the "Chart of Accounts Statement" file?

Invoice SC .zip

[Mats]

I have made some modifications now to the db, mainly in "Chart of Accounts" as attached!

/Mats

Invoice SC .zip

[Fenton]

Well, your accountant has a good idea I guess, centralizing all the transactions into one Chart of Accounts file. I'll bet however that he is not a database designer. He is used to software that automatically does this, behind the scenes.

FileMaker is not accounting software. It is flexible. You have to build what you want. There needs to be a foolproof mechanism to ensure that every relevant transaction is sent/imported/whatever into that file. If it misses even one edit, you're screwed.

You say you don't want it to be an import mechanism such as the VAT_Status, and would prefer that it is created with a new record. That means that you have to reliably capture all input to that record, and pass it to the Chart file. You cannot allow any editing to the record without passing those edits to the Chart (I guess real serious accountants would say you have to create a separate record to record the edit, but that would take another file).

From what I can see you have fairly simple files. There are several, but each is simple. The VAT_Status routines we did earlier are the only complex routines in the solution. Data entry is done straight into the fields in all other files.

If you want to do the Chart file, you are going to have to do two things:

1. Lock down the interface. No data entry or editing to relevant fields allowed outside of your strict control.

2. Pass all relevant data to the Chart file.

If it was me, I'd create global fields for all relevant data entry, and flip to a layout for any editing, trapping them on the layout with a Loop/pause routine (and remembering to choose the Halt option on the button to leave, at least before you turn Allow User Abort [Off].

Then you can easily trigger a script that sets that data to the Chart file, as well any custom validation you need to do on the entry first.

It's a different kind of interface. It can however look pretty much like the normal one, if you copy the normal one to create the global one, placing it in the same position. FileMaker does not redraw objects that are in the same exact position. Which means don't move things around after copy/pasting, just redefine the relevant fields to be the global fields; set any data that was in the regular fields into the globals; or, alternatively, clear the globals for a new record.

You don't need to have globals for irrelevant fields, just those that affect the Chart. The others can just be as they were. You don't really need them on the layout, but the redraw is probably less noticeable if they are.

You can either have "New" and "Edit" buttons, or you can create scripts that flip layouts, then take them to the same field they clicked on (intuitive, but tedious, depends on how many fields).

Just how badly do you need this Chart? Unless you're willing to make it foolproof it would be better to not have it, because it will be incorrect.

[Fenton]

On the plus side, if you do institute the Chart Account entry from the other files, then the script could set the VAT_Status entry on the side; so it would not need its update scripts.

[Mats]

Thanks for your reply Fenton!

Ok I think I will try to use/make one "Chart of Accounts Statement" and see how it goes.

First thing first. How do you suggest to use the best lookup from the Chart of Accounts file to all the transaction files?

[Fenton]

There are two basic concepts you need to understand.

1. Lookups only occur in the data entry file. The mechanism for the lookup is the modification of a field. So technically it makes no sense to say "How do I lookup to another file?" I know what you want to do; but I do not know where data will be entered, so I can't say how to make the transfer happen. I do know that it will be scripted.

I explained how I'd do it, with global fields and scripts. I was assuming data entry was being done in the individual files, pertaining to their subject matter, then transferred to the Chart file. It could also be done in the Chart file. But the same principle applies. See 2.

2. You are asking about creating an entire set of "redundant" data. That is, the same data in two different places; not a related "view" of the data, but an actual copy of the data. There is no way to make FileMaker create the same actual data in two places without a script. A lookup does not do this (see 1.)

If you enter the data in the Chart, by simulating layouts for each of the other files, the same rule applies. Either the data is entered into related fields, auto-creating records in the other files; but NOT in the Chart file itself. Or it's entered into fields in Chart; but it will not be in the other files unless passed via a script. You cannot trigger a lookup in the other files without a script.

As I said, accountants are used to dedicated accounting software which either does this or appears to do this.

3. The Chart account is therefore not going to make data entry safer. If you decide to implement it as redundant data it will allow you to see whether you've implemented it correctly.

It could, by providing a one-stop view, help in some way to find something. It will also provide some cash flow reports that would not be easily available in the other files. In other words, if you're implementing it as a "report" file, or as an "import/export/synchronization" file to other software, then that's another thing.

As the latter type of file, you have the choice of either synchronizing an "update" of the data, or using scripts that run at the time of each data entry (and edit). You've seen the former method in VAT_Status, and I've explained the latter with global fields. Now you have to decide which/what/whether you want to do.

[Mats]

I was not very clear in my last post. What I meant to ask was how shall I use import/lookup the account numbers from the chart file. Not thinking about the actual statement file for the moment (in some sense yes for further use).

I.e. if you see the Takings file I already made some fields for account numbers and VAT rate for each account number. As every Bar Sale will have the fixed account no. 3200, Food Sale 3201 and so on, I assume that a lookup can be used for this purpose, OR just to have them there without a lookup.

In some other cases like Purchases, the account number will be entered manually for obvious reasons, as different purchases can have many different account no's.

I hope you understand what I try to explain with my poor English, and I will try to figure out your last post again what method to use

[Fenton]

OK, I see now what you're doing. You can ignore my last two long and tedious emails, other than as reading material.

You're trying to tie the Chart of Accounts into the Takings file. The Takings file is really kind of a hack, with hard-coded fields for each type of account that you need for a day's totals. (And the totals are coming from somewhere outside the system?).

In any case, if you want to do it that way, you need to get the Account#. There's two ways. You could hard-code it, to whatever it is in Accounts, either as an auto-enter text, or as a calculation (which, if you changed would change for all of that code). You hard-coded the field name already, so it's more or less the same. It's a hack, to put it mildly; but it would work. If, however, you changed the code in Accounts, but didn't here, the link would be broken.

A more correct way to do it would be to create a field in the Chart of Accounts, a marking field for "use in Takings." Then, in Takings, creating an Import script that first Finds those records in Accounts, imports them, then sets their date to whatever date you want (current date usually). So, if there's 6 Accounts, you get 6 records per day. Each one has the Account # and name. Rather than 1 record per day you have 6. Then you have a correct structure, which may make a big difference down the road.

Takings SCP.zip

[Mats]

I think in any case if I where to use a lookup I have to change the Chart of Accounts file again, as i.e. the "Bar Sales-3200-15%-S" don't have it's own fields.

All account details is put in the the same fields "Account Name-Account Number-VAT Rate-SC"

This means that any lookup would be impossible I think? The reason for using a lookup from the Chart of Accounts in stead of a "hard core" code as auto enter text in Takings would be if a VAT tax rate would change for a particular item only. Then I just have to change it in one place.

I just try to be a bit forth seeing

[Mats]

Hi Fenton,

Are you still alive? I am and still going about with my DB with some help from a accountant oriented FM guy

I will quote what he wrote in the mail so you understand better I hope:

Invoice CS.zip

[Fenton]

(I gave it its subject back, though it's not very descriptive; it should be "Recreating Quickbooks" -)

Yes, I'm still alive. Somewhat. I've been creating an "eBase" type solution for a non-profit. Not really tricky, but lots of files.

I think you can do what you want. I saw, without getting into gruesome detail, that you had some extra fields in your Nominal Ledger, which you were trying to make work. That's more or less the method.

The trick you missed was to include the "plain" number field in the calculation. Here are the players:

Net

Gross

VAT

Also, normally:

_cNet = Gross - VAT

_cGross = Net + VAT

_cVAT = Gross - Net

For any given transaction, 2 of the numbers will be plain, and one is calculated.

Each file that feeds into this central file will have its own pattern for data entry, which must be enforced, because entering data in all 3 could leave 1 wrong. So the 3rd number is always only a calculation field, different fields in the portal for different files. No record in Nominal Ledger will have conflicting patterns on any given record.

Change the calculations to:

_cNet = Case ( not IsEmpty(Net), Net,

not IsEmpty(Gross) and not IsEmpty(VAT), Gross - VAT,

"")

_cVAT = Case( not IsEmpty(VAT), VAT,

not IsEmpty(Gross) and not IsEmpty(Net), Gross - Net,

"")

_cGross = Case( not IsEmpty(Gross), Gross,

not IsEmpty(Net) and not IsEmpty(VAT), Net + VAT,

"")