Hi Guys and Happy New Year!

I have a small problem (again) with my db.

I created a field Named "VAT Amount" as a calculation (VAT * Net Amount)

BUT it's not always that I want that calculation and then I want to be able to enter another VAT Amount manually. I set the field to "Allow entry into filed" But I'm still not allowed to modify that field

You cannot modify a calculation field's result.

You may want an "override" field, a regular number field.

VAT_override

VAT Amount = Case (VAT_override > 0, VAT_override, VAT * Net Amount)

On the layout put VAT Amount on top of VAT_override. VA Amount is not enterable, VAT_override is. The cursor will end up in VAT_override.

You could alternatively put VAT_override by itself on the layout, next to VAT Amount, non-printing, so you know when you've overridden the result.

Thanks Fenton,

Another way I found out just nos is to make the "VAT Amount" Field a normal number field and then do the calculation in Auto-enter-Calculation. That will make the calculation based on the VAT rate given, but will also be modifiable. Is there any reason why not to use this solution?

I also want you to have in mind that I will continue using this VAT Amount field in other layouts with relationships. I.e. this layout will be like a statement where I can see How much VAT I paid to that or all the Suppliers. Will that be possible with the two fields example you gave me?

I am truly glad for your support Fenon

If it's an auto-entered calculation it will not change once it's been triggered. This would be a problem. What if you entered a VAT rate, then changed it? Or added an invoice item? It would not recalculate.

It's best to use a straight-ahead calculation, so you can be sure it's accurate.

As it is, if you override the VAT total, then the VAT rate showing on the radio button will no longer be valid. Since it does not print, this may not matter. But you have to be careful not to return inaccurate results, different from what the user would expect from their actions and what they can see.

That's why I'd either show the VAT_override separately also, as non-printing field, or an unstored text calculation stating that it's on. That makes it clear what's going on. Unless you don't want anyone (including yourself) to see that it's an override. You could conditionally hide it from unauthorized viewers.

Well!

Your solution works great. Thanks Fenton!

I just made the VAT Override in blue color so I can see when it's in use

But why do you use underscore "_" between the words? (VAT_override)

Could you please help me with another calculation that I use for a similar file.

I want to use your suggested formula:

[color:"blue"]Case(VAT_override > 0, VAT_override, VAT * Net Amount)

but for a new field named "Paid Override". The Paid field now use the formula:

[color:"blue"]Case(PaidButton; Gross Amount)

Hi Mats,

Fenton is following good field-naming conventions. Spaces generally aren't considered a good idea when naming fields. Most of the time you can get away with spaces, but should you ever want to publish a FileMaker file on the web, you'll really wish you'd gotten into the habit of using the underscore character! Since you can use any kind of text you want for a field name once it's on a layout, using the underscore character can be invisible to your users.

Yeah, I don't like spaces much, even though I seldom even think of the web. I often don't use the "_" either, just cram it all together; depends on the field, whether it's still readable.

Following up on John's comment, that you can change the name on the layout; I would add that if you do use spaces, so the name is the same as the label on the layout, then if you change the field name later, the corresponding label on the layout will change also, automatically. Often this is not what you want, esp. since you don't see it happening; it could happen on many layouts. So it's actually safer to not have the field name the same as the labels.

Unless of course you want them the same, and you are creating a new file. It's a toss-up. But I prefer to break the link. A lot of times I'm working on someone else's file, and I don't like their field names, so I change 'em. Or I'm modifying a "template" file of my own, for a different use.

As to:

new field named "Paid Override". The Paid field now use the formula:

Case(PaidButton; Gross Amount)

It doesn't really have to do with the PaidButton (which is an odd name, I'd call it PaidFlag, because I often have global container fields with buttons in them, which I use the suffix Butt for -)

What you want to override is the Gross Amount.

Gross_Override

Case( Gross_Override > 0, Gross_Override, Net Amount + VAT Amount)

I don't follow you on the Gross_Override suggestion?

Related to the Invoice file I have a Statement file where I use the field's: Gross Amount, Paid and Outstanding. The Paid field is related to a check box and when checking that the Paid fields just gets the whole Gross Amount and the Outstanding gets to 0.

Now if fore some reason the Costumer only pais a part of the Invoice I have to be able to override the Paid field and manually insert how much is paid.

Did I misunderstand you some how there Fenton?

I also started a new project but should I start a new thread for this question?

So now I have two files "Invoice" and "Purchases". In Purchases I register all the Purchases dun in the Company. Now I want a third file where I can gather all information on VAT in and out, getting the general idea of how much I have to pay in VAT to the tax authorities.

How shall I make the structure of this file with relations and all. The biggest question for now is how I will get a new record in my VAT file each time i create a new invoice or purchase record?

(Please let me know if I should start a new thread for this)

I thought you wanted to "override" the total. But that's not what you meant apparently.

You already have an override for the Paid amount. It's called Paid Changeable; same thing more or less. You are using that field in later calcs to figure out what is still due. So it's already done.

No I don't have a proper override field. The "Paid Changeable" is a Auto-enter-Calculation field which you just explaned to me why not to use

Of course I tried to modify my Case(PaidButton; Gross Amount) calculation my self to an override field, but so far I did not manage to figure out how

I think the Paid Changeable may work OK on this. A big difference is that you only receive payment on an Invoice once,* whereas you change the items on it often. So when you click the Paid checkbox, both Paid (which is a calculation, which you wrote above) and Paid Changeable populate (the same).

If you override Paid Changeable then it's different. The only problem would be if you unchecked PaidButton. That would rarely happen. In that case Paid would be empty, but Paid Changeable would remain.

You could make the checkbox a scripted "toggle." That way you'd be able to delete the Paid Changeable if PaidButton was toggled off; also to control when/if someone could do that. (I changed the name to PaidFlag, that's what I'd call it.)

Actually you would no longer need 2 Paid amount fields then; the script could set the Gross Amount into the Paid amount field. It would be just a number field then, so it could be overriden right there. I think that's what I'd do. It's less confusing, and somehow it's more logical to not have the Paid amount be a calculation.

You'd need to modify further calculations to use the Paid field.

*For partial payments, you have to edit manually. If it becomes much more complex you may need a separate payments file and a scripted Loop to mark invoices paid, if you cannot do it manually. This is no fun; because you then have to be able do it in reverse in case of mistakes; it makes me nervous.

Here's a complete "toggle" script for PaidFlag. It includes a check for the "Admin" group, which is a good security measure. It also allows access for Find mode. Just attach it to the PaidFlag checkbox (which no longer needs to be enterable).

[x] Toggle PaidFlag Script

If [ Status(CurrentMode) <> 1 ]

Darn. That should just be "Paid" not "PayAmount," though "PaidAmount" would really be the best name.

I will work on that script

Do you have any hints how I could structure the new file recording VAT amounts paid and received from the Invoice and Purchases files?

A separate file just for recording VAT amounts? All the entries, or just show totals?

It would be easiest (and safest) to just have a "shell" of a file, with little real data, and just show the totals from the other files via relationships, using the Sum(relationship::number field) function.

This could be done in each file, but I'm assuming you want to see the totals from both files, and do some arithmetic with them.

It could have relationships built on date calculations: month(date), year(date), etc., with a record for each time period you were needing to total. Easy enough to have a loop create new records.

The only real problem with such a related structure is speed. But filtering the data by date blocks should keep it from slowing to a crawl. Depends on how many entries, and how often you need to see the total.

If you need to mirror the actual entries, the difficulty would be in creating records and pushing/pulling the data from the other files. Whenever a VAT amount was entered or overriden in the other files, something would have to happen in this file.

You'd need either a plug-in that can run a script when a field is changed, or some very locked-down layouts for data entry in the other files (non-scripted editing, overrides for example, being particularly a problem).

The structure of such a file would not be difficult. It would have the payment itself, an ID from the Invoice file or Purchases file, and a clear identity of which it was from.

I will first try to give you more input what I want the new "VAT" file to do for me.

It will only be related to two files. Invoice and Purchases. And then each time I create a new record in one of the two above files a new record will also be created in the VAT file with some fields such as: Company, Date, Invoice No. Net Amount, VAT Amount and Gross Amount.

This new file will be used as a form off balance sheet with the possibilities to see some statistics such as, VAT, Gross Amount etc. for a chosen period etc. The sorting by date to and from etc. will be used within the file. The amount of records will not be to many (Max 2000 records total per year)

My first problem is how to exact trigger the function so that a new record is created in the VAT file automatically when making a new record in one of the two other files? What fields/Relations are the key triggers?

I think you just explained this in your post above, but you have to remember that I am a newbie and English is not my first language so I'm sorry if I don't catch up so easy

With the PaidFlag script I gave up and will stick to the Paid Changeable solution as the script was a bit tricky and you said that it was ok as is

I understand why this file would be useful for reports. But I don't think you need the details in there, because of the difficulties involved. You do not really need to compare VAT's of Invoices vs. Purchase Orders on an "each" basis, do you?

I was saying that creating a "shell"* type file, for aggregate viewing or reporting purposes would be not too difficult. It would use relationships built upon date entities to show Sums of the related values in the other 2 files. You could do simple arithmetic with the results. It would show you what you need to know, as an overview.

If you need more detailed reports of each, they could be done in each file separately. You can pull the total from the other file via a Sum(relationship::field) if you need it, using a date range.

Creating a file that imports this data from each file would not be too difficult, as far as structure goes. But it would involve some kind of dynamic synchronization method, which is not so easy. In fact, if the other files can be edited afterwards, then it's quite difficult.

Or just delete all the records and import the entire batch at one time. Guaranteed to be synchronized, but only at that moment.

And then each time I create a new record in one of the two above files a new record will also be created in the VAT file with some fields such as: Company, Date, Invoice No. Net Amount, VAT Amount and Gross Amount.

If you create a new VAT record, there is, at that moment, no data for it, because it hasn't been filled in yet. And there is no easy method in FileMaker for it to get the data later, at least not without a script; and every script needs some mechanism to trigger it; which would have to run after any edit that changed the numbers.

*I'm making up the name "shell" for this kind of file. It just means a file with little real data, often a fixed range of dates or date/times, which, via relationships, can show data from other files. Most calendar type files do this. They don't have the data in the file itself; it just looks like it.

Mats, I apologize for not really hearing what you were saying, which was that you want the VAT file, with the details. There is a fairly simple mechanism. If you have FileMaker 5.5 or later (I believe) the Import dialog has a "Update Matching Records" option.

If you create a VAT record for each Invoice and each Purchase order, at their creation time, passing nothing but the ID, then you could use that mechanism later to update the VAT records.

It wouldn't particularly matter if you created the record and passed the ID or not, because you would also check "Add Remaining Records" in the Import dialog (just like a regular import).

You would need to do 2 Imports, one for Invoice, one for PO (obviously). You would need to have a found set of the records you wanted data for in each of the 3 files; imports only work with the found set.

In each import, click a couple times on the respective ID field, until it turns to a <=> double arrow (match field).

It is not the speediest of operations, the Update option (it's actually probably faster to just import all the records). But with only 2000 records and a few fields it would be plenty fast enough.

It would actually have some advantages over your current Invoice file, where the totals are Unstored. In this VAT file the totals from the other files could just be regular number fields. They are not changeable. Makes further analysis very fast.

Hi Fenton,

I have made an example db to show you what i mean. This works fine and das what I want it to do EXCEPT that the "Trigger" field that make the relationship active and create a new records in the VAT file has to be filled in manually now.

I can't figure out a way to make it a working Auto Enter/Serial No. field? Maybe this is not a good solution but it's as far as my FM knowledge allows me to do

This is what I had in mind. The main operation is the Import script, using Update Matching Records, with the InvoiceNum as the match field, and Add New Records options.

Put this file into the same folder as your Invoice.fp5 file. It may or not import correctly; I can't be sure the fields will match up, but they should (I set it up with your older Invoice file).

To see what happens in the import, open the script script and uncheck the "Perform without dialog" checkbox.

Yes, it's a little tricky to work with. But you really should understand how to work with this dialog, or you'll never be able to add any fields to the VAT file.

It's very much like a Find or Sort script step, except that it can pause in the middle for you to change things; if you uncheck "Perform w/out dialog" (the others options, "Restore Order" and "Specify File" generally stay the same after the first time).

But, after changing it and running it successfully, immediately open the script again, select the Import step, and check the "Perform without dialog," then close.

*** When it asks whether to "Keep" or "Replace" Import Order, be sure to click REPLACE. It's very easy to just hit OK, with the default "Keep," but then all your changes are GONE (incredibly easy to screw up here). ***

I always put an Import step as its own little modular script, so I never see that dialog unless I need to.

VAT_Report.zip

So..

Now I tried to make a script as you suggested, and it works to some extent. I will attach everything for you to see.

The problem comes to the LineItem/Description which is in another file named "LineItems" I tried as you can see to find a solution, but it did not work

But I want your confirm that this is how you meant it to be and how to proceed after this And IF LineItems and Purchases can be implemented in this file? No I don't have to compare VAT item to item just totals of certain periods?

I made a few changes. You didn't do the Import as I said. It has to be Update Matching, and it has to Show All first, or you're going to end up with a lot of duplicates.

As I've said, this type of thing is not really for beginners. You're going to have to read the posts carefully and analyze everything that's been done.

I also removed your graphics folders. You'll need to replace them.

Please do not include folders of large graphics when you post to these forums. It's not designed for people to conduct business, passing large solutions back and forth. We are all just guests on this computer.

I'm not sure whether the Invoice VAT numbers and the Purchase numbers should really be in separate fields or not. Either way would be OK. It depends. They are separate right now, 'cause it's easier to understand that way.

You need to decide whether to bring in such things as Company and Seller names. It's a lot of text if you have no reason to search/sort, etc. on it. It can be shown as related fields.

There is no reason to bring in Line Items. You cannot have records from Line Items and from Invoices, since they are basically the same thing; more duplication problems. Why would you need Line Items in a summary report file?

I'm having trouble getting the file to attach. Trying again.

Invoice DB.zip

I have now been looking at the structure of what you sent me and changing some things for a good 3-4 hours. And I'm going crazy over some things...

1. In the original you sent me there was an small error so that every time you clicked Report there was an extra set of the Purchases record implemented.

2. There where missing some fields in Purchases that i inserted.

3. The Purchases file also have an "VAT_override" which has to be in the Report as I did not fix yet.

BUT the problems I face with the script is:

1. When un checking the "Perform without dialog" to add the new fields etc. The list to the right (VAT Status fields) ALWAYS changes for me and will not stay as they where as "Last" in the popup menu.

2. I change by hand first for the Invoice report and then for the Purchases Report and click Import. That works fine.

3. Then I run the report script again (and all the fields are mixed up again) I the check the Match records and Add and click Import again. That also works fine.

4. Then I go back and check the box "Import without Dialog" and run the Report script. AGAIN all the matching fields are mixed up....

And belive me I've tried many ways to hold the script I want!

I attach the db again so you can see also how I changed the structure as I want it (not ready yet but you will get the idea)

When you click OK after setting Import without Dialog, select Replace for the Import setup or it will use the same order as when it was created.

The problem was the 2nd import, from Purchases, needed its own Show All Records step. After the Invoice Import, of course you had a found set of whatever new Invoice imports, so the Purchases import would bring in extra records.

I brought all the scripts together in your 1 Reports script; it was the last thing I did; it's always that last thing that goes wrong. It should be done from 1 script, so it can run all the needed Show All's.

You must have a full found set in all 3 files at the time of each import for the Import Matching Records to work. So I added the step/scripts in all 3 files. Someday you may want to narrow it down to a date range; but it'll be OK for a while.

Yeah, the Import dialog can be tricky. I wrote in an earlier post how to do it. As you've found out, you have to deal with it to add fields to the VAT file, or change the import order.

The operations have to be done in a specific order. It may be easier for beginners to just delete the darn thing, import successfully, create a new Import script (with only 1 step, Import Records; best to not mix this step with others, make it modular).

But that's a pain. You lose the order and the targeted file, and have to redirect other scripts that point to it.

So, to modify an existing import:

1. Open the script

2. Uncheck [ ] Perfom w/out dialog

3. Close the script

4. Run the script (can do it right there in ScripMaker)

5. If successful, open the script

6. Check [x] Perform w/out dialog

7. Close the script

8. It will ask "Keep" or "Replace" order?

9. REPLACE

10. OK

11. Never open it again

12. Until you have to

Invoice DB.zip

Hi Fenton,

And thank's again for your patience with me

Now it's was working fine except that the Company Name from the Purchases was filled in twice as the Company Name field for Purchases was created as an relationship field. But I just excluded the import of the Company Name in the Purchases script so now it work fine

Now I think I'm almost getting ready with my db as I want it. The thing remaining now is to make some sort/show script for the VAT Status file.

1. Show from/to date

2. Show chosen Company Name from/to date

Is it possible to make a script that makes a popup window with the options i.e.

from date > to date

Company Name > all

11. Never open it again

12. Until you have to

Or at least don't click OK and Replace the next time you do. Clicking Cancel or OK (and not 'Replacing' anything) shouldn't cause a problem.

So, you managed to modify the Import Purchases script, and it still works? Congratulations, now you're a developer -/

Yeah, I saw that the "seller name" was also shown as a related field. I didn't know exactly which, if any, name you wanted from Purchases, and what it would be used for later. It sounds as if you want the company name from Invoices, but not the seller from Purchases? (that makes sense to me).

As you've seen, you can just put a related field on the layout, without even defining it (well, it's already defined in its own file). Or you can can actually pull in the data, via an Import, or with a Lookup mechanism.

[You can also define it as an Unstored calculation, based on the relationship. This allows it to be shown in portals in other files.]

Which method you use depends on what you want to use it for, primarily, and how much execution time/disk space/programming it takes to get and store it.

It sounds like you do want to analyze by company, from only Invoice VAT? In that case you probably do want the company name [though at this point I must tell you that serious databases use an ID for the company, not just a typed name. Any typo will mess up your summaries. Also, an ID takes up a lot less space. But that requires a dedicated customer file with IDs.]

You should look at what a SubSummary part, using Summary fields (I put 2 in already) is in the Help. I can't really set one up, 'cause I don't really know what to do about the company/purchase company names; I don't know if you even want the purchases company name.

Yes I'm a developer now, hehe

The last bug now is that it wont sort the record by date, even if I try to manually

Most important for now is to have a sort function in the "VAT Status" file that allows me to specify the time period I want to see and analyze. I.e. 1'st of June to 31'st of August (a VAT period) so I can see the exact VAT balance.

Then I was wondering if the "Report" script maybe also should be executed when opening the file, and then be updated with the button?

I again attached the db so you can see the final result how I wanted the structure of the Vat Status file

It's not really a "sort" function to narrow down the records, it's a Find. (Of course, you'd probably want to Sort afterwards, by Date anyway). It's a date range Find. If done manually, you enter "date1..date2" into the Date field (we'll leave out scripted Finds for now).

It looks good. But I believe you're painting yourself into a corner with the separate names and dates.

I say, use the company name and date fields for both of the imports. Do not have a separate Purchase_Date field. Yes, import the Purchase name into the Company Name field.

It's one thing to have separate fields for the numbers, because it looks/works better for reports. But having separate Company and Date fields makes it a pain.

If both imports use the same Company and Date fields you can show them on the layout as one field. But even more important, you can Find, Sort, and use Subsummary parts more easily.

I've included these things in these files. Please use ALL the files (or transfer Show All scripts to your files), 'cause there are 2 Show All scripts in Invoices and Purchases that are part of the Import process, called from VAT Status, that I believe got lost when you transfered to your files.

It's difficult transfering pieces of solutions unless you are very careful.

Invoice DB.zip

Now I got the point regarding the Company/Seller field. (I'm still learning even if I'm a Developer now) hehe

But why did you delete my "VAT_Balance" Field? Thats where I know the VAT Balance and is the most important

I also learned how to use the find to specify periods, but I don't think that it's very user friendly. Can it be done in a simpler way?

Now I have to start fix all buttons, Layouts/Design and menus so I can start using my db from this year

What I am thinking of now is where to implement salaries and the Z takings (Which also is a part of the VAT Balance) etc. so I can have the total turnover of the Company in one db, That way it will be so easy having control and to be up to date all the time.

Do you have any suggestions where to put the Z result from the cash machines. Can/Shall I use one of the existing file or create a new?

Now I got the point regarding the Company/Seller field. (I'm still learning even if I'm a Developer now) hehe

But why did you delete my "VAT_Balance" Field? Thats where I know the VAT Balance and is the most important

I also learned how to use the find to specify periods, but I don't think that it's very user friendly. Can it be done in a simpler way?

Now I have to start fix all buttons, Layouts/Design and menus so I can start using my db from this year

What I am thinking of now is where to implement salaries and the Z takings (Which also is a part of the VAT Balance) etc. so I can have the total turnover of the Company in one db, That way it will be so easy having control and to be up to date all the time.

Do you have any suggestions where to put the Z result from the cash machines. Can/Shall I use one of the existing file or create a new?

Now I got the point regarding the Company/Seller field. (I'm still learning even if I'm a Developer now) hehe

But why did you delete my "VAT_Balance" Field? Thats where I know the VAT Balance and is the most important

I also learned how to use the find to specify periods, but I don't think that it's very user friendly. Can it be done in a simpler way?

Now I have to start fix all buttons, Layouts/Design and menus so I can start using my db from this year

What I am thinking of now is where to implement salaries and the Z takings (Which also is a part of the VAT Balance) etc. so I can have the total turnover of the Company in one db, That way it will be so easy having control and to be up to date all the time.

Do you have any suggestions where to put the Z result from the cash machines. Can/Shall I use one of the existing file or create a new?

I didn't delete your VAT_Balance field. I just checked the files you uploaded. No VAT_Balance field. So recreate it. It is a calculation field, hence has nothing to do with the imports. Now that you're a developer, a few rules:

1. Never have 2 copies of a file ANYWHERE on your network with the same name. If you have more than 1, compress the ones you're not working on. If they're obsolete, throw them away. FileMaker can find and use the data of any file of the name referenced, esp. in relationships and external calls, with no error and no warning. It usually doesn't, but it could. It's not worth the risk.

(It's kind of a problem with files named "Invoice.fp5". I have 8 on my computer. These are mostly example files. None of my real invoices files are named that. I use some prefix to distinguish them.)

2. Know which of the 2 copies is the one that will be your master file. It makes no sense to make changes to a file, then confuse it with another and make different changes to the other. I have a feeling you have another VAT_Status file somewhere with that field.

3. Add the field to MY files I sent you. It is a waste of time if you lose the changes I make, then add others features of your own to separate files, and post those. This is especially a problem if the file calls external scripts in other files, as this one does; those scripts will be broken if you put the file in with other versions of the solution that don't have the scripts. They'll still call an external script, but it will not be the right one.

4. Never delete a field after you've stored scripted imports for a file. Just mark the field as obsolete; you can reuse it for something later. Deleting a field wrecks the import order and it has to be redone and saved again.

[Example: Purchase_Date is no longer being used. It's obsolete. Do NOT delete it. You could rename and redefine it as a calculation field for VAT_Balance (with a number result).]

I have no idea what z takings are. I'm from Southern California (San Diego). Our sales tax system is overseen by a big guy who's famous for fighting robots (well, he was once one himself, but that was long ago -)

If you are going to import every line item from cash machines you are talking about a lot more records. The Import-Update routines demand that you synchronize the entire file with the other entire files. This will slow down considerably after a few thousand records. It may seem fine in a test file with only a few records, but it will be unbearably slow in the real world.

It would be more appropriate to summarize those records by a date, say 1 day's worth, then create records with a script and set that number. It could also be an import-update; it would be only 365 records/year. It would require some intermediate techniques: Sum(self-relationship::number), Loop-Compare-Omit, match on Date-z calculation field.

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

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.

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

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

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.

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

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

/Mats

Invoice SC .zip

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.

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.

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?

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.

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

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

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

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