Creating a record in a different table based on a field value

[MichaelE]

Hi,

I'm new to the FM world, coming from a background in SQLServer and DB2. I'm refactoring a legacy v6 app, and I'd like to know if the following is possible. The logic is:

If FieldValueinMainFile = 1,

Create New Record in Related File 1

Else

Create New Record in Related File 2

The original legacy app has 400+ fields in a single table (?!), most of which are calculation fields which do different processing of the data entered in the layout, based on what the user selects from a dropdown list. I'd like to split these calculations out into separate tables/files to get rid of the big switch statements, both to maintain my sanity and that of the maintenance programmers.

Thanks,

Michael

[Ender]

You can do this by defining a new script in ScriptMaker, that has steps like you have above. To create the new records in the other files, you'll need to define separate scripts in those files and have this script make an external call to each of those creation scripts.

I'd like to split these calculations out into separate tables/files to get rid of the big switch statements, both to maintain my sanity and that of the maintenance programmers.

You should take care that you're not simply moving the same fields to another file just to have fewer in the main file. A relational structure should be used to eliminate the inefficiencies of the original flat file.

[MichaelE]

> defining a new script

Your suggestion was considered, but the users would balk at any changes to their current interface or procedures. Right now the user has to create a new record in order to see the field that has the drop-down list I mentioned. Is it possible to trigger a script based off a selection in a list?

>relational structure should be used

This legacy app is a reproduction of a form issued by the state to allocate energy assistance grants to the poor. It collects personal, demographic, and financial info from the client, converts it into another format that is exported to a comma-delimited flat file, which is them uploaded to the state. I've been able to refactor out the obvious things such as creating a ZIP code table where the ZIP is the primary key to eliminate the duplicate city, county, and state info, but a lot of the processing is in the form of, "get the first 12 characters of the client's last name".

The whole thing is a bit of a mess and the users are *extremely* resistant to any changes to the interface or their workflow. Plus there's no documentation, no schema, and nobody has a clue what the business rules are, that is to say, "normal contract gig." *smile*

Thanks,

Michael

[Søren Dyhr]

The original legacy app has 400+ fields in a single table (?!),

Yes there are more systems so poorly designed, than I wish to think of ...but a lot of this is done because relational design not is embraced in particular.

When such files land on my table, will I usually say that - if I'm going to give it an overhaul is it very very likely that it's gonna cost more to fix than to develop a new one from scratch.

Keeping the workflow and usual layouts intact is not the big deal here, they can be pasted over in a newly created solution. But you're absolutely right that something could be done to the herds of calcfields referencing each other ...and rightly should.

The real trouble is when you try to migrate such a solution to 7.0 where especially the summary fields are likely to be a lot slower. But often is it a tough job, to disuade executives from the vanity ... the bare thought of an upgrade is likely to perform worse than previous when MISUSED for ages is simply not sinking in.

it possible to trigger a script based off a selection in a list

No natively, but portals will ... but it's a part of the wrong flat structure. This is a FAQ to say the least I would say in top3

Plugins can remedy this ...but my personal opinion is that it's wrong userinterface wise.

It's not the job of a lifetime thats landed on your path, if you can't make a living otherwise, tough sh*t!!! I would deny to do any thing, but build it from scratch...

--sd