Selecting value list item copies data to another field. HELP!

[BillyTD]

Hi,

  Its been a long time since I had to create a FM db.  I don't know how to make an automated copy and paste work.  Maybe it is renamed or changed to something else now in FM12.  I just want to make a General Journal type file for small business to reconcile checkbook for tax preparation.  It categorizes the income and expenses and tells the user how much they have spent in each category for the year.  I know, pretty boring.

  Here is my problem.  I can't remember how to make a value list automatically copy data upon selection from the "Amount" container to its corresponding category field.

  I have tried what I thought was right and have failed.  I was searching through this forum and have tried some relational stuff...still not quite getting it.  I think FM Pro used to be easier or I'm just getting dumber.

 

Here are my value list items:

Sales

Loan

Draw

COGs

Advertising

Bank Fees

Commissions

Insurance

Interest

Legal

Office Exp

Rent

Repairs Maint

Supplies

Tax / Licenses

Travel / Meals

Utilities

Other

 

I want the db to copy and paste data from the Amount field into whichever field is selected from the Category value list.  I sent a cleaner copy in my attachment that leaves out the big mistakes that i was making today.  I have given this to a few people that wanted it and they all hated that it didn't do the auto-fill thing.  I've used this for years.  i guess I'm just used to the extra steps...lol

 

Can anybody help me to design the right way to do this?  I'm clueless.

I would like to give it to other small business people if they want it for free too.

Small Biz Tax Expense Prep.zip

[eos]

I don't know how to make an automated copy and paste work

 

That's good, because, for several reasons, you shouldn't use C&P. If the user does it, that's one thing; but as a developer, you should employ other methods.

 

To transfer values from field A to field B, use Set Field [ Table::fieldB ; Table::A ].

 

In your many-similar-fields (MSF) approach, assuming that the fields have the exact! same names as the value list items, you could use

 

Set Field by Name [ Get ( LayoutTableName ) & "::" & Table::category ; Table::amount ]

 

to avoid having to write an immense block of If … ElseIf … End If.

 

Having said that: the MSF approach will get you nowhere pretty fast; handling those fields, extending the list of possible transactions and doing simple things like reports become very complicated and require constant adjustments.

 

I recommend creating a Transactions table where you store your transaction types (the current Category value list) as records, and reference them via a foreign key. This has the added benefit that users could define their own transaction types.

 

TransactionTypes --< Transactions >-- Account >-- People

 

Then your Transactions table consists simply of fields for date, transactionTypeID, accountID, debit/credit (which you could even store with the transaction type), and a single amount field (plus a single summary field for total).

 

This makes your current task of post-processing user entries moot; instead you'll present the user with a list of categories (as a dropdown, or a selection portal in a popup window etc.), and after they have entered an amount and selected a category, you create a related Transaction record for the account, using the transactionTypeID of the selected category.

[BillyTD]

Thank you eos.

 

  If I create a transaction table, will that work with the users that import data from a csv from their bank?  Most users that I gave this to import to use this as a tool to prep expenses and income to put on their schedule C tax document form.  My goal is to keep it simple.

[eos]

If I create a transaction table, will that work with the users that import data from a csv from their bank?

 

Why shouldn't it? After all, what you have now already is a Transactions table, albeit a complicated one. Every line from the csv becomes a record in a table – with the difference that now the transaction category isn't specified by the field the amount (eventually) goes into, but a category field, so you don't have to transfer the amount from a generic amount field into the 'correct" field.

 

All in all, in your existing file you could do away with all the specialised fields and keep the generic amount field plus the category; and if you really want to keep it simple, don't use a transactionsType (category) table, but just import the subject line into the category. That depends on the reporting requirements of your users, and the need for consistency or using generalised categories.

 

My goal is to keep it simple.

 

For whom? The users, or you?   “Simple is hard”, when it comes to programming. Usually a normalised data structure requires less work to get the desired results, not more. 

 

btw: Please add your OS and FileMaker version to your profile; this can be helpful information for your potential responders.

[BillyTD]

Yes, I wanted to make it easier for me!  I haven't developed in FMP since 7 and then I was still mostly a flat-filer.

This is a once-a-year use database.  People import their bank csv and I just want to allow them to just use one click per line so their data from their "Amount" field replicates into the field supplied by the category field.  If they click sales in that field, boom the amount is displayed in the sales field.

If you look in the scripts, there is where I started.  I need an easier approach that works.  If you have an idea for a script, please advise.  I dont know how to write them beyond that.