Script for automating bookkeeping

[MikeAir]

Hi,

I'm trying to create a FM-script that would create a record in table A (Kassekladde), each time a person presses a button, from another layout which is based on table B (Posteringer).

Here's the relationships:

And here's the script:

However, it only creates the two pre-defined values "999999" and "11111111", and the rest of the fields are empty..

I've tried a lot of different things, like using a global field as the primary key, but this didnt work as well.

 

The idea behind this setup is to make some pre-defined (scripts) bookkeeping steps, where I only have to push a button, each time I want a particular type of transaction (not sure thats the right word for it in english).
However, any help with hints about what i am doing wrong/could make it work would be greatly appreciated as this would speed up a time-consuming task quite a bit :-) 

Thanks,

Mike

Edited May 9, 2015 by MikeAir

[eos]

However, it only creates the two pre-defined values "999999" and "11111111", and the rest of the fields are empty.​

That's because the new Kassekladde record is not related to any Posteringer record, and so these field references can't get any data; you need to set the foreign key, i.e.

Set Variable [ $posteringerID ; Posteringer::ID ]
Go to Layout [ Kassekladde ( Kassekladde) ]
New Record/Request
Set Field [ Klassekladde:FK_posteringer ; $posteringerID ]
etc.

Note that it only makes sense to create the same fields in Kassekladde if you want to change their values, or capture a “snapshot in time” of these data in Posteringer (since they will change); otherwise, you can always access and display the related “original” fields in Posteringer.

Edited May 9, 2015 by eos

[Rick Whitelaw]

It looks like all the fields with the exception of ModKonto already exist in the related table. What have the data exist in two places?

[MikeAir]

That's because the new Kassekladde record is not related to any Posteringer record, and so these field references can't get any data; you need to set the foreign key, i.e.

Set Variable [ $posteringerID ; Posteringer::ID ]
Go to Layout [ Kassekladde ( Kassekladde) ]
New Record/Request
Set Field [ Klassekladde:FK_posteringer ; $posteringerID ]
etc.

Note that it only makes sense to create the same fields in Kassekladde if you want to change their values, or capture a “snapshot in time” of these data in Posteringer (since they will change); otherwise, you can always access and display the related “original” fields in Posteringer.

​Thanks! That worked perfectly! I havent been using the set variable function in a script yet, so today i've learnt something new  

It looks like all the fields with the exception of ModKonto already exist in the related table. What have the data exist in two places?

​The reason is that I want that, is because the "Kontokladde" table us purely for exporting 5 fields in a CSV-file for an online accounting-api that I use.. So I wanted all the raw data in "Posteringer" and all of the transactions in "Kontokladde" ready for export.. Hope that makes sense..

[comment]

​The reason is that I want that, is because the "Kontokladde" table us purely for exporting 5 fields in a CSV-file for an online accounting-api that I use.. So I wanted all the raw data in "Posteringer" and all of the transactions in "Kontokladde" ready for export.. Hope that makes sense..

​No, I am afraid it doesn't. Normally, you export data from existing tables, and there should be no need to duplicate it just to export it. Moreover, if there ever was such need (in some imaginary scenario), then the duplication should be handled by the exporting script, and not depend on someone remembering to press a button. Finally, when you have  a script that creates a duplicate "each time a person presses a button" you run the risk of that person (or another one) pressing that button again (and again). That's just not a good method, in any scenario.

[MikeAir]

​No, I am afraid it doesn't. Normally, you export data from existing tables, and there should be no need to duplicate it just to export it. Moreover, if there ever was such need (in some imaginary scenario), then the duplication should be handled by the exporting script, and not depend on someone remembering to press a button. Finally, when you have  a script that creates a duplicate "each time a person presses a button" you run the risk of that person (or another one) pressing that button again (and again). That's just not a good method, in any scenario.

​Then let me explain (and thanks for questioning, this made me question the usefullness of the idea).

I've been given some of the responsibility of accounting on a music-festival in dk. All of the voluntary workers have been given acces to a credit card when they need money for materials etc. So they will log on to a fmhost filemaker db, where they via a simple formular type in the details of their purchase, and maybe even upload an image.

After this, i have to verify the data they have entered, and do the accounting (debit/credit). They idea is that I create a popover button with different buttons, performing different scripts. Each of the scripts represent a debit/credit action, i've been pre-programming. So when i do the revision of their data, i only have to correct mistakes, and do the very seldom/unpredictable debit/credit actions.

This should enable me to easily walk through hundreds of annexes, as long as they are classified the right way: I guess this means explaining each alternative in an understandable way for the end-user, so that he/she can easily tell the difference.

After the revision, the data is going to be exported as a CSV-file in a particular format(semicolon), and then imported to a danish accounting-system called dinero.
I tried this procedure yesterday, and so far exporting from filemaker and importing in the accounting-system have worked.

But youre right, this solution introduces redundancy. However, im not sure if this is a problem when I am the only one who does the revision of the data.