It is currently 2 March 2021, 16:22 Advanced search

Working with flat files

Questions and answers on how to best use Instant Developer

Working with flat files

Postby Ted Giles » 12 January 2021, 12:52

I am importing a flat file which is supplied as a list of data;
Example

Road Name
House Number
Number of residents in the House
Number of occupants in the Road

On a single form, I need to display - by Road - the House Numbers and number of occupants in each house as well as the summary of occupants for the Road.

Example

Cornwall Road
House 1
Residents 3
House 2
Residents 4

Road Total = 7

Ideally, the form will be in 2 sections, the Road selector at the top and the House metrics below, so each road will have all of the details shown in the lower panel.
Silly thing is that I can do it if the tables were separate, but I am struggling with a simple flat file which I don't want to split into two for various reasons.

Any ideas?
User avatar
Ted Giles
 
Posts: 500
Joined: 29 November 2013, 12:58
Location: The Wolds, Lincolnshire, UK

Re: Working with flat files

Postby ljwilson » 12 January 2021, 14:32

Ted, I would try to normalize the data into two tables, one of which could be an imdb table depending on size. If you are showing on a form the road and then the children of it, and the number of children is relatively small then an imdb table for the children is a possibility. Creating two classes for each of them is also a possibility, using a master query on the primary file for each to logically divide them into parent/children.

If the number of records is in the thousands, I would simply create two other db tables on the backend and load the primary file into them as needed, but you say you don't want to do that. If the reason is you don't have control over the backend database then you could create your own sqlite db stored on the server and use it instead for the additional tables.

...jack
ljwilson
 
Posts: 559
Joined: 26 November 2013, 14:15

Re: Working with flat files

Postby Ted Giles » 12 January 2021, 14:41

I will try creating 2 tables, Jack.
I am responsible for the extract, but it makes more work.
Still, that might be the best option.
User avatar
Ted Giles
 
Posts: 500
Joined: 29 November 2013, 12:58
Location: The Wolds, Lincolnshire, UK

Re: Working with flat files

Postby john w » 12 January 2021, 15:12

If the flat file works for various reasons. I would think you could split the form create two panels. Then as may be appropriate drag the table to the top panel and hide those fields you do not want to show. Repeat for the bottom panel. I suspect you would not need to relate the panels. The same rows, or parts of the row, show both places.

Sums should be straight forward.
Regards

John W
User avatar
john w
 
Posts: 629
Joined: 24 October 2012, 16:29
Location: Apple Valley, Minnesota USA


Return to Tips & Tricks

Who is online

Users browsing this forum: No registered users and 6 guests