General Form Query

Hi. I have a form for capturing data for Timber Moisture Content and one of the fields is Date Ready for Treatment. If we take an example of 100 Bales of timber are checked for moisture, 20 are suitably dry so the user records the Date Ready for Treatment. Next week, of the remaining 80 bales that are checked a further 20 bales are ready. Is there a way I can record the date ready for treatment for the second 20 while maintaining the date ready for treatment for the first 20, all on the same record.

@MauriceWhelan
Will you be creating new records for each or will you be editing an existing record?

Edit existing record each time

@MauriceWhelan
Provided you don’t mind the data having in comma separated form i.e. “3/2/2019 , 3/3/2019 , 3/4/2019”, yes it’s possible. Firstly add a change timestamp type column, bind it to [Date Ready for Treatment] column for capturing each change in this column. Then you can CONCATENATE these change timestamp values for that record.

Thank you very much @LeventK I will take a look

@MauriceWhelan
I have build a sample app for your use case. You can check the Record Changes app from my portfolio page.
https://www.appsheet.com/portfolio/245151

After testing, provided the proposed solution fits your needs, please visit this post again and from this display, click the green checkbox next to your name in the main menu so that it will mark your proposal as SOLVED and your link will be filtered away from this sample app.

2 Likes

Thanks a million @LeventK. I will take a peek.

You’re welcome @MauriceWhelan, enjoy.

Hi @LeventK. I am trying to incorporate a CONTAINS Expression into an OR Expression but can’t seem to get the syntax quite right. I have OR(CONTAINS([Module] = “Works Orders”), ([Module] = “Production”), ([Module] = “Retention”)) which I know is a little off. Could you advise on the right structure?

@MauriceWhelan

OR(
CONTAINS([Module], “Works Orders”),
CONTAINS([Module], “Production”),
CONTAINS([Module], “Retention”)
)

Thanks so much

You’re welcome @MauriceWhelan, enjoy.

Hi @LeventK. I have added in the columns as you advised and used your formula in the Date Ready for Treatment Changes. My formula reads SPLIT(LOOKUP([_THISROW].[Record ID],“Moisture Check Timber”,“Record ID”,“Date Ready for Treatment Changes”))+
IFS(NOT(IN([Date Ready for Treatment],SPLIT(LOOKUP([_THISROW].[Record ID],“Moisture Check Timber”,“Record ID”,“Date Ready for Treatment Changes”)))),
LIST([Date Ready for Treatment])) but the form is not showing the Date Ready for Treatment Changes field. Hoping you could advise? Thanks.

Hello @MauriceWhelan
Are you using a slice with your form? Provided you are using a slice, endure that your [Date Ready for Treatment Changes] column is included in the slice.

Hi @LeventK. Thank you for the response. I have it working a little. Still not happy with the behaviour. Couple of issues I am having

  1. I noticed in your sample app that when you select the new DATE_FOR_TREATMENT it updates the DATE_CHANGES field within the form. In mine it only seems to add in the new DATE_FOR_TREATMENT after I save the record. When I return to the record to add another DATE_FOR_TREATMENT value the DATE_CHANGES field is empty on the form.
  2. I want the format of the value in the DATE_CHANGES field to be DD/MM/YYYY like your sample but it is appearing as ‘Fri Mar 29 2019 00:00:00 GMT+0000 (Greenwich Mean Time)’ in mine.

Could you help?

@MauriceWhelan
In my sample app, [DATE_CHANGES] column is an ENUMLIST type with both Allow other values and AutoCompleteOtherValues properties are set to ON. Item separator is set to " , " (space comma space), Base type is Date. The below expression is in AppFormula:

=SPLIT(LOOKUP([_THISROW].[ID],"Record Changes","ID","CHANGED_DATES"))+
IFS(NOT(IN([DATE_FOR_TREATMENT],SPLIT(LOOKUP([_THISROW].[ID],"Record Changes","ID","CHANGED_DATES")))),
LIST([DATE_FOR_TREATMENT]))

Hi @LeventK. Think it is sorted now. Looks like it is working correctly now :grinning: Many thanks again for your help. It really makes a difference to get help on these things.

1 Like

Hello @MauriceWhelan, glad to hear that you have solved it. Truly my pleasure to be helped of. Can you please mark my post as solution provided it suits your requirements and solved the issue so that other members can also benefit out of it when they search for the “solution” tag in the community. Thanks.

Hi @LeventK. If I have [Description] field in 2 different tables, is there a way I can compare these 2 columns and present the differences for a specific date period? Apologies if I should be directing this as new topic.

Hi @MauriceWhelan
Can you please elaborate your query a bit more? Yes of course you can compare values from 2 columns from different tables conditionally, but provided you can be specific, I will try to help more. Thanks.