I want to modify my field

[Mats]

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

[Fenton]

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.

[Mats]

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

[Fenton]

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.

[Mats]

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)

[John Caballero]

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.

[Fenton]

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)

[Mats]

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?

[Mats]

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)

[Fenton]

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.

[Mats]

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

[Fenton]

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 ]

[Fenton]

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

[Mats]

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?

[Fenton]

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.

[Mats]

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

[Fenton]

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.

[Fenton]

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.

[Mats]

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

[Fenton]

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

[Mats]

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?

[Fenton]

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?

[Fenton]

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

Invoice DB.zip

[Mats]

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)

[-Queue-]

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.

[Fenton]

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

[Mats]

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

[-Queue-]

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.

[Fenton]

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.

[Mats]

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

[Fenton]

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

[Mats]

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?

[Mats]

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?

[Mats]

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?

[Fenton]

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.