Exporting to Excel - What if 2 Related tables w/ mutliple records?

[deego55]

I was wondering if this is possible (seems like its not to me, but maybe there is a workaround)?

I have a table (Task). It has two related tables (Budget and Funding). Both the Budget and Funding tables can have multiple records for a task.

Is there any way to do an Task export to Excel that will contain all of the Budget records and all of the Funding records?

I understand how I can pull from either the Funding Table or the Budget table individually, but not sure if its possible to include both on the same export for a task.

Thanks for any help!

[mr_vodka]

Since there are no UNION capabilities with FM, you can try and create a temporary table to dump the data into and export that way.

[deego55]

thanks, thats exactly what i ended up doing.

is the only way to "dump" the records into the table by doing an "Import Records" script step and point to same file? if so, is there a standard way to point to itself?

thanks!

[deego55]

I now have a new dilemma. I created a table that stores the records from the two tables and is then used to produce the extract. Each time the extract script is run i delete all the records in the new table first (prior to importing).

This works well, EXCEPT this is going to be used by several users on a shared database. If two users run this script at the same time, the correct records will not be extracted, as the new table is supposed to be populated with related records for that specific person. if they are run at the same time, all records for both people may be included.

Is there a way to prevent two users from running the script at the same time? or is there any other way around this?

[mr_vodka]

Well there are a couple of ways to handle this. You could allow for multiple people to do it at the same time by adding in a created by field. However, if you have lots and lots of records and you have to delete and reimport for each user the record count can get high.

Another thing you could do is create a one record table for locking. You can have it so that a user will check to see if that record is locked. If not, then you make a field a active so that it locks the record. You can then open a new window, go to the layout that you want to delete and reimport and do your process. Once finished, you would have the script close the window and then commit the orig background window.

So if a user tries to run the process while the first user has the record locked, with an error trap you can prompt the user that it is busy.

[deego55]

hi vodka. thanks for the reply. i actually am trying the created by technique, but i dont have it working yet.

i like the record locking technique, but am not sure if i am advanced enough to figure that out.

[_henry_]

Hi Deego,

Try to read this thread...

http://fmforums.com/forum/showtopic.php?tid/191268/post/280031/hl/lock/fromsearch/1/#280031

Hope this helps...