I have a table with a date and a description. The date is selected via the date picker on a custom form that writes to a temporary table to check if the date has already been used.
My problem is that, I have created a relationship between the date in the main table and the temp table but nothing shows when the dates are the same. Both fields are set to date only (short format). If the time somehow getting checked (even although I can’t see it) is there an in-built function just to record dates without the time?
Yes, almost certainly that is the case.
When you set “Date only” on a date/time column, that only affects what is displayed. The full date/time value is still stored under the hood, and used in any computed columns.
What you can do is use a Math column to convert both dates to integers, and use the Math columns in your relation. Use the following:
Sorry to be a pain. I have created the math column and this now working fine but I can’t get my head around what I am trying to achieve now.
I have 2 tables. one with dates and other fields. The other checks if the date has already been used.
In my custom form, I have bound this to the date checker table and added the date picker. When I click submit, it adds a new row to the date checker table and the relation field advises if this date has been used.
I don’t want the submit button to show if the date has been used and I’m not sure how I accomplish that when I need to write the data to the table first to check.
I also want to write the data back to the main table if the date hasn’t been used but I can’t see how I can do that in the custom form when it’s bound to a different table.
You said that you are using a Custom Form. So that means you are writing the selected date to a User Specific column, and then using that with an Add Row action, right?
Assuming that’s the case, the relation should be formed (or not) before the user clicks submit.
If you’re actually using a native form, then what you can do is target the date picker at a column in your User Profile row, and build the relation from there. Then use the selected date as a user profile value with the form.
If you’re not sure what type of form you have, show me a screen shot.
I originally had it as a in-line form. I then changed it to a custom form and yes, I am using the add row action. I don’t understand how the relation can be formed before the user clicks as the submit button is what’s used to write the data to check in the first place.
Because that’s how a Custom Form works. Inputs are written to User Specific columns so that they can be used (amongst other things) for pre submit validation. I’m guessing that you’re not really using a Custom Form. And if that’s the case your only option is to switch to one - because you don’t have user profiles. Again, if you show me a screenshot of your form layout I’ll immediately be able to tell.
As soon as you select a date, itis written to the table that is the source of that screen, correct? That is where you should have your relation. If the relation is empty, show the submit button, else it will be hidden.
No, I have to press submit before the data is written to the table.
EDIT: okay, it looks as though it is writing the date but to the wrong table. I’m getting confused by what’s supposed to happen.
What I have is 2 tables. one with dates, comments, players, rollup scores etc… (call it table1)
I have another table (table2) which just has dates and relationship columns to determine if the date already exists in table1
The tab is linked to table 1 but I believe the form should be linked to table 2 so that when a date is entered, I can check if it exists. What seems to be happening is, when the date is selected, it’s adding it to table 1 so it’s not checking if it exists. I guess I can change the source to sort that but what I don’t know is how do I then write the data back to table 1 if it’s determined that the date chosen has not been used before?
A native form stores entry data in temporary memory. A custom form is not a native form. It is a detail screen connected to an existing row of data. Your custom form (detail screen) instantly stores your entry values directly in the table row that the screen is attached to as you type. The Add Row action attached to your button takes those values from that table and writes them to a new row in a destination table. With a custom form, you are working with live data as opposed to a native form which is not attached to any live data.
I prefer having a separate table that only serves the purpose of driving your custom form. It sounds like you are trying to duplicate the all dates between two tables. You don’t need that. Table 2 should be a single row helper table with user specific columns to store your entry values for the custom form. Your custom form should be attached to Table 2. Table 2 should then have a relation linking the entered date to existing dates in Table 1. If the relation is empty and the entered date is not empty, then show the Submit button. (You should never have more than one row in Table 2)
I’m not trying to duplicate the fields. the second table is just a holding table to verify the date hasn’t been used. Once it’s populated table1, the data in table 2 will be deleted. I think I’m almost there, I’ve just go the data sourced the wrong way round.
The custom form only seems to allow data to go into table 1 rather than table 2 (the one used for checking). I’m not sure how I point the custom form to an alternative data source.
Change the action that opens the form. I assume you have it set to ‘Show New Screen’ → ‘This Item’, but instead change it to ‘Show Detail Screen’ → ‘Table 2’. You can can still use ‘Show New Screen’, but it probably doesn’t really matter. ‘Show Detail Screen’ locks the screen layout to the table which is useful if you need to reuse the same form elsewhere in your app. ‘Show New Screen’ gives you a new screen layout for each instance that you use it, even with the same table.
Isn’t that what the submit button is for? To submit your form data to Table 1? The date should already be in Table 2 the moment you selected a date from the date picker. The relation should have checked for any matches in Table1 at the same time. As a result, the Submit button should only be visible if the relation is empty.
If you share some screenshots of your progress, it might be easier to see where you are stuck. Mostly I’d be interested in seeing the the configuration for your custom form screen. It the form screen attached to Table 2? What column is the date picker in that form writing to? How is the relation configured in Table 2? What does your visibility condition look like for your Submit button? How is the action configured for the Submit button?
Hi Jeff, I have parked the date thing for now as I’m trying to sort something else out.
I have a table of players which I have linked to a choice component. Scores can be entered for these players based on a date which has been selected. What I am trying to do, when a player has been selected and entered, the choice component should remove that player from the list,
I have created a relation column which shows which players have been selected from a date which I thought I could use to filter the choice field. The problem is I can’t reference a relation column in a choice field. Does that make sense. I have created a template if you want to see what I’m trying to achieve here. If you don’t have time to look but could point me in where I’m going wrong, that would be helpful.
