Advice on how best to export multiple containers from multiple related tables

Questions
1

Problem description

I am exporting container fields from 5 related tables. The amount of the related table counters to be export can be set by the user. for example the user might set the first 2 records from table1 but only 1 record from table2. This would repeat though all the found records of the main table.

My current method

  • User defines settings like how many to export of each related table
  • Starting at the first record I go to related table1 in a new window showing only related records and export the number of containers the user set.
  • repeat the above step for related table1-5 the user selected to export from.
  • Now repeat the above step on the next record.

I would just leave this but I tried to be fancy on my original setup and used Set Field By Name and while this did work I find that I do not like how easy it can go wrong if I change a field name. Seeing as I decided to rebuild it I wanted to see if I could build it better.

My first thought was to get a list of all the records IDs using GetNthRecord and store them in a variable $Table1 as a list. Once I had all the records I wanted to export in a list I could then go find them and export them.

This method would really decrease the amount of times I need to open new windows but to my surprise there dose not seem like a great way (with out making a new relationship) to find all the records at once. While this would work I do not feel this is likely to be all that better then my original idea. I did learn how to use GetNthRecord though so that made it worth it.

My next idea was to reverse the idea, instead of using GetNthRecord to store the ID fields I would use SetNthRecord to tag each record making it supper easy to then go find the ones I want to export. Why dose this script step not exist?

This brings me to posting here to see what others suggest. Was my original idea just fine and no need to find a better method? I mean it dose work and all the screen flashing can be ignored.

2

Two options:

  1. Don't hard code the field name, use Get functions to determine what the field name is.
  2. Hard code "labels" and calculate field names.

A calculation to determine which field name to pass into SetFieldByName could look like this.

Let ( 
[
	  _apple = GetFieldName ( Apples::name ) 
	; _orange = GetFieldName ( Oranges::name )
] 

	; Case (

	  Get ( LayoutTableName ) = GetValue ( Substitute ( _apple ; "::" ; ¶ ) ; 1 ) 
	; _apple

	; Get ( LayoutTableName ) = GetValue ( Substitute ( _orange ; "::" ; ¶ ) ; 1 ) 
	; _orange

	)
)

When using labels your code would look a bit different.

Let ( 
	_targetTable = Get ( ScriptParameter )

	; Case (
	  _targetTable = "apples" 
	; GetFieldName ( Apples::name )

	; _targetTable = "oranges" 
	; GetFieldName ( Oranges::name )

	)
)
1 Like
3

This is very interesting. It is amazing how many ways you can do the same thing and even more amazing how crafty some of them are. Thank you!

This will make my original method more resilient while still not having duplicate code. but I still feel there must be a better way. Am I wrong? is this how others would do it as well?

4

Assuming your solution is a multi-user solution, I am left wondering how you manage the user selection of related records. Your description gives me the impression you use a checkbox-like field in the related tables. If so, that is not multi-user safe.

I would typically use a years old technique in a case like this: create global fields in the parent table for each portal that would contain record IDs of records to export; create buttons in the UI that manage these fields; use the global fields in relationships and use GTRR to display only the records that need exporting.

You would still need to generate five windows for five relationships, but that would be the only windows that need to be managed. No need to repeat for each parent record.

Hope this helps.

5

I am constantly wonder just how much details about my database structure I should share when asking these question. While on one hand it seems I never share enough on another it seems like a endless pit of information oh and my own laziness.

I constructed what I call a saved export section to my solution. All the settings live in its own table. The user can navigate to this section and save an export with a export name and settings and then run it when ever they need it. Once saved anyone that wants can use the saved export it is not user based.

I have a relationship setup that lets me pull the settings while exporting but I am testing saving all the settings in JSON when running so that I will no longer need the relationship. Not sure I will be happy with that choice but I do try and keep my relationships clear of clutter. This seemed like a good relationship to remove as it is only used for this one task.

It also exports more then just containers and handles searching and more. My plan is to have it have an option to schedule the export but I ran out of time the last time I worked on this section and when the run on server did not work right off I stopped. I hope to add that soon though.

I built this for many reasons but one of the main uses is for importing to other programs. It is common for a subset of data that needs to be imported. I have talked to my customers about doing direct links but all my them like the celerity of understanding from a CSV file.